Creating the Division Management page
The next page in the report is the Division Management page. This page will be used by division managers within the organization to display relevant information so that they can keep their division utilized.
To create this page, perform the following steps:
- Right-click the Executive Summary page and choose Duplicate Page.
- Rename Duplicate of Executive Summary to Division Management.
- Pam plans on this page being available for drill-through. So, fi st, copy and paste the Back button from the Template page onto the Division Management page. Note that when pasted, the Back button conveniently appears at the same location on the page.
- Next, click on a blank area of the canvas and expand the Visualizations and Fields panes. Drag and drop the Division column from the Hours table into the Add drill-through fields here area. Since this page already contains a Back button, Power BI does not add one automatically to the page.
Pam knows that it will also be important for the division managers to track utilization by the type of employee. To add this visual, perform the following steps:
- Select the % Utilization by Division visual and replace Division in the Axis field well with the EmployeeType column from the People table.
- Use the Format sub-pane of the Visualizations pane, expand the Title section, and set Title text to % Utilization by Employee Type.
Pam knows that the division managers will want more detail than at the Executive Summary level. This means tracking hours and utilization by individual job codes. To create this visual, do the following:
- Select the KPI visual and change this visual to a Table visual.
- Replace Month in the Values field well with the JobID column from the Hours table.
- Replace Target Utilization Value in the Values field well with the Hours column from the Hours table.
- In the Values field well, ensure that the columns from top to bottom are JobID, Hours, and then % Utilization. If not, simply click on the columns in the Values field well and drag and drop them.
- In the column header area of the Table visual, click on the edge of the table and drag the % Utilization column all of the way to the right of the visual, but not so far that a horizontal slider appears at the bottom of the visual.
- In the Format sub-pane of the Visualizations pane, expand the Column header section and change Font color to #E5EAEE, Theme color 3, Font family to Segoe UI (Bold), and Alignment to Center.
- Expand the Total section and change Background color to #3C3C3B, Theme color 2 and Font color to #E5EAEE, Theme color 3.
-
Switch back to the Fields sub-pane, right-click % Utilization, and choose
Conditional formatting and then Data bars.
-
In the Data bars dialog, change the Positive bar color to #3C3C3B, Theme color 2
and click the OK button.
- Click on the % Utilization column in the Table visual to sort by descending order.
- Click on the % Utilization column one more time to sort by ascending order.
Pam also believes that the division managers will want to see the total hours that were spent on different tasks. To create this visual, follow these steps:
- Select the Total % Utilization card visual and shrink this visual vertically to about half its original size.
- Copy and paste the Total % Utilization visual and reposition the visual directly underneath the original.
- Change this to a Clustered column chart visual.
-
Replace % Utilization in the Values field well with the Hours column from the
Hours table.
- Put the Category column from the Hours table into the Axis field well.
- Use the Format sub-pane of the Visualizations pane, expand the Title section, and set Title to Hours by Category.
- Change the Color values for X axis and Y axis to #3C3C3B, Theme color 2 and toggle both axis Title settings to Off.
Pam realizes that if users drill through to this page, they will not have any visual cues as to what division has been drilled into. Pam decides to create a dynamic title measure and visual. To create these items, follow these steps:
-
Right-click on Calculations and choose New measure. Use the following formula:
Divisions Title =
VAR allCount = 3 VAR currentCount =COUNTROWS(SUMMARIZE(‘Hours’,[Division]))
RETURN
SWITCH(
TRUE(),
HASONEVALUE(Hours[Division]),MAX(Hours[Division]), allCount = currentCount,”All Divisions”,“Multiple Divisions”
)
This measure uses a special form of the SWITCH DAX statement, where TRUE() is specified as the first parameter instead of a column or measure. This allows you to specify multiple complex logical statements that return either true or false. The first statement that returns true returns the corresponding calculation.
- Shrink the Table visual vertically from the top to make room in the top-left corner of the page.
- Click on this blank area of the canvas and then click on the Card visualization in the Visualizations pane.
- Reposition and resize this visualization to take up the blank space.
- Add the Division Title measure from the Calculations table to the Fields area for the visual.
-
Use the Format sub-pane of the Visualizations pane to toggle Category and
Background to Off.
-
Expand the Data label section and change Color to #7EAE40, Theme color 6, Text size to 24, and Font family to Segoe UI (Bold).
Picking a green color from the theme instead of the same title color as the other visuals provides a visual indication that this text is different than the titles for the other visualizations.
The completed Division Management page should look as follows:
Figure 7.12 – The Division Management page
Pam is satisfied with the Division Management page. To finish this page, do the following:
- Create a bookmark for this page by selecting the View tab of the ribbon. Then, in the Show panes section, select Bookmarks.
- In the Bookmarks pane, choose Add and rename this bookmark Reset Division Management.
- Select the ellipsis (…) for this bookmark and choose Group.
- Rename the group Division Management.
- Close the Bookmarks pane.
- Select the Reset button in the lower left of the dark gray rectangle.
-
In the Format button pane, expand the Action section and for Bookmark, choose
Reset Division Management.
The Division Management page is now complete.
