Learn Power BI Creating the Branch Management page

Creating the Branch Management page

Pam also wants a page more focused on branch managers. Much of the information that’s displayed, as well as the layout, will be similar to the Division Management page. To create this page, follow these steps:

Right-click the Division Management page and choose Duplicate Page.
Rename the Duplicate of Division Management page Branch Management.
Pam plans on this page being available for drill through. Therefore, click on a blank area of the canvas and then expand the Visualizations and Fields panes.
Remove Division from the Drill though section and drag and drop Location from the People table into the Add drill-through fields here area.

Pam knows that the branch managers will also be interested in how their branch is performing across divisions, as well as their overall percentage of utilization. So, she can leave the Total % Utilization, Hours by Category, and % Utilization by Employee Type visuals as-is. However, the other visuals need to be changed slightly. To do this, perform the following steps:

Select the % Utilization by Branch visual.
Replace Location in the Axis field well with the Division column from the

Hours table.
In the Format sub-pane of the Visualizations pane, expand the Title section and change Title text to % Utilization by Division.

Pam wants to create a similar dynamic title measure for branches as she did for divisions. To do this, follow these steps:

Right-click on the Calculations table in the Fields pane and choose New measure.
Enter the following formula:

Branch Title =      VAR allCount =

COUNTROWS(SUMMARIZE(ALL(‘People’),[Location]))

VAR currentCount = COUNTROWS(SUMMARIZE(‘People’,[Location]))

RETURN

    SWITCH(

    TRUE(),

HASONEVALUE(People[Location]),MAX(People[Location]), allCount = currentCount,”All Branches”, “Multiple

Branches”

)

Select the card visual in the top-left corner and replace Divisions Title in the Fields

field well with Branch Title.

Pam also needs to change the Table visualization. To do so, follow these steps:

Select the Table visual.
In the Values field well, replace JobID with the Name column from the People table.
In the Values field well, right-click % Utilization, choose Remove conditional formatting, and then All.
Shrink the % Utilization column in this visual so that it is just wide enough that the % Utilization text does not wrap.

Pam wants to create a colored indicator for the Table visualization denoting whether or not each employee is meeting Target Utilization. Pam previously created a Meets Goal measure that returns 1 if % Utilization is greater than or equal to Target Utilization and 0 if not.

Th s measure can be used to create a graphical indicator. To do this, follow these steps:

Drag the Meets Goal measure from the Calculations table into the Values field well so that % Utilization now appears twice.
Double-click the Meets Goal in the Values field as well in the Fields sub-pane of the Visualizations pane and change its name to Status.
Click the drop-down arrow for Status in the Values field well and choose

Conditional formatting and then Icons.
In the Icons dialog under Rules, change the first rule to be is instead of is greater than or equal to. The value should be set to 0. Then, change Percent to Number.
Delete the second rule using the X icon on the right.
Edit the last rule to be is instead of is greater than or equal to. The value should be set to 1. Then, change Percent to Number.
Under Icon Layout, change the dropdown from Left of data to Icon only.
Click the OK button to close the dialog.

Pam is happy with the icons but does not like 1 appearing in the Status column’s Total

row. To fix this, do the following:

In the Format sub-pane of the Visualizations pane, expand the Field formatting

section.
Change the dropdown to Status.
Set Font color to #3C3C3B, Theme color 2.
Change Alignment to Center.
Toggle Apply to total to On.

The completed Branch Management page should look as follows:

Figure 7.13 – The Branch Management page

Pam is satisfi d with the Branch Management page. To fi sh off 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 Branch Management.
Select the ellipsis (…) for this bookmark and choose Group.
Rename the group Branch 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 Branch Management.

The Branch Management page is now complete.

Creating the Hours Detail page

Pam now has three pages that summarize information for managers at a high level. But she knows that there will be times when these managers will want more details about employees and how their time is being spent at work. Therefore, she decides to create a page dedicated to showing the breakdown of hours by employee. To start creating this page, follow these steps:

Right-click the Template page and choose Duplicate Page.
Rename Duplicate of Template to Hours Detail.
Pam plans on this page being available for drill through. Therefore, click on a blank area of the canvas and expand the Visualizations and Fields panes.
Drag and drop the Location and EmployeeType columns from the People table and the Division column from the Hours table into the Add drill-through fields here area.
Select a Matrix visualization from the Visualizations pane and expand this visual so that it takes up the entire canvas not consumed by the CONTROLS area.
Place the Name column from the People table in the Rows field well.
Place the Category column from the Hours table in the Columns field well.
Place the Hours column from the Hours table in the Values field well.
Since there is a lot of information to fit onto one page, use the Format sub-pane of the Visualizations pane to reduce Text size to 12 for the Column headers, Row headers, Values, Subtotals, and Grand total sections.
While in the Format sub-pane, toggle Background to Off.
In the Values area, rename Name to Employee.

Pam notices a problem, however: the order of the categories is alphabetical, so they’re not in the same order that the managers are used to seeing in other reports. To fix this, do the following:

Start by clicking on the Modeling tab of the ribbon and selecting New Table.
Enter the following formula for the table:

tmpTable = DISTINCT(‘Hours'[Category])
This creates a table of all of the distinct values in the Category column of the Hours table, ensuring that all of the categories are present and that all of the spellings are the same.

Switch to the Data view, right-click this table in the Fields pane, and choose

Copy Table.
From the Home tab in the Data section, click on Enter data. Enter data queries provide a convenient mechanism so that you can hand-enter short lists of data.
Press Ctrl + V. The rows of the table will be pasted into the first column and the first row of the table, Category, will be promoted to a column header!
Rename the table Categories using the Name field.
Click on the + column to add a column and rename this column Order.
Provide the orders shown in the following screenshot. When you’re done, click the

Load button:

Figure 7.14 – Create Table using an Enter data query

A new table, Categories, will be added to our model.

Switch to the Model view and confirm that Power BI has added a relationship between the new Categories table and Hours. If not, add the relationship between the Category columns in both tables.
Right-click tmpTable, choose Delete from model, and, when prompted, choose Delete.
Switch back to the Report view.
Expand the Fields pane and select the Order column in the Categories table (not the check box).
Click on the Column tools tab and ensure that the Order column has a Data type

of Whole number. If not, set Data type to Whole number.
Click on the Category column in the Categories table (not the check box).
From the Column tools tab, click on Sort by column and choose Order.
Add the Category column from the Categories table to the Columns field well

for the matrix and remove the original Category column. The column headings are now sorted in numerical order based on the Order column.

Pam likes this visualization but also wants to see % Utilization as a total. To do this, follow these steps:

Add % Utilization from the Calculations table to the Values area of the matrix visualization.
In the Values area, rename % Utilization to %.
Unfortunately, this adds a second level under each Category, with each Category
now containing two sub-columns for Hours and %.
- To fix this, switch to the Format sub-pane of the Visualizations pane, expand the
  
  Values section, and toggle Word wrap to Off.
- Now, in the matrix visualization, use the top Category column header row and carefully shrink the columns for each Category except the Total column, until % is no longer displayed.
- In the Format sub-pane of the Visualizations pane, expand the Column headers
  
  section and toggle Auto-size column width to Off.
- Finally, click on the % column to sort this column in descending order.

Since this page will support drill through, Pam wants to use her dynamic title visuals. To use these visuals, follow these steps:

Shrink the matrix visual vertically from the top to make room at the top of the page.
Copy the Division Title card visual from the Division Management page and paste it onto the Hours Detail page.
Now, copy the Branch Title card visual from the Branch Management page and paste it onto the Hours Detail page.
Arrange these cards across the top of the page and adjust their horizontal size to be a third of the width of the matrix visual.

Since the users can also drill through on EmployeeType, Pam needs an additional dynamic title measure. To do this, follow these steps:

Right-click on the Calculations table in the Fields pane and choose New measure.
Enter the following formula:

Type Title =

    VAR allCount = 3      VAR currentCount =

COUNTROWS(SUMMARIZE(‘People’,[EmployeeType]))

RETURN

    SWITCH(

    TRUE(),

HASONEVALUE(‘People'[EmployeeType]), MAX(‘People'[EmployeeType]),

allCount =    currentCount,”All Types”, “Multiple

Types”

    )
Copy the Branch Title card visual and paste a copy onto the page.
Arrange this card visual across the top and replace its Field properties with the

Type Title measure.

The final report page should now look as follows:

Figure 7.15 – The Hours Detail page

Pam is satisfi d with the Hours Detail page. To fi sh the page, perform the following steps:

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 Hours Detail.
Select the ellipsis (…) for this bookmark and choose Group.
Rename the group Hours Detail.
Close the Bookmarks pane.
Select the Reset button in the lower left of the dark gray rectangle.
In the Format button pane, toggle Action to On.
Expand the Action section and for Type, choose Bookmark.
For Bookmark, choose Reset Hours Detail. The Hours Detail page is now complete.

Creating the Employee Details page

In addition to the Hours Detail page, Pam wants another page focused on displaying detailed information for one or more employees.

To create this page, do the following:

Right-click the Division Management page and choose Duplicate Page.
Rename the Duplicate of Division Management page to Employee Details.
Pam plans on this page being available for drill through. Therefore, click on a blank area of the canvas and then expand the Visualizations and Fields panes.
Remove Division from the Drill through section and drag and drop the Name

column from the People table into the Add drill-through fields here area.
Select the Hours by Category visual.
In the Fields sub-pane of the Visualizations pane in the Values field well, right-click Hours and choose Show value as and then Percent of grand total.
Click on the Format sub-pane and toggle Data labels to On.
Expand the Data labels section and change the color to #3C3C3B, Theme color 2.
Expand the Title section and change Title text to % Hours by Category.
Delete the % Utilization by Employee Type and % Utilization by Branch

visualizations.
Click on a blank area of the canvas. Now, select the Date column from the Calendar table and then add the % Utilization measure from the Calculations table to this visual.
In the Axis field for this visual, right-click Date and change this from Date Hierarchy to Date.
Now, change this visual to a Line chart. Expand the visual horizontally and vertically to consume the entire bottom of the light-gray area of the report page.
Use the Format sub-pane of the Visualizations pane and toggle Background to Off.
Expand the Title section and change Title text to % Utilization and Forecast.
In the Title section, change Font color to #3C3C3B, Theme color 2, Alignment to

Center, Text size to 24, and Font family to Segoe UI (Bold).
Change the Color values for X axis and Y axis to #3C3C3B, Theme color 2 and toggle both axis Title settings to Off.
Finally, switch to the Analytics sub-pane, expand Forecast, and click +Add.

Since this page also uses drill through, Pam wants to create a dynamic employee title measure. To create and use this measure, perform the following steps:

Right-click on the Calculations table in the Fields pane and choose New measure.
Enter the following formula:

Employee Title =

    IF(

    HASONEVALUE(‘People'[Name]),

    MAX(‘People'[Name]),

    “Multiple Employees”

    )
Select the card visual at the top left and replace the Field properties with

Employee Title.

The final report page should now look as follows:

Figure 7.16 – The Employee Details page

Pam is satisfied with the Employee Details 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 Employee Details.
Select the ellipsis (…) for this bookmark and choose Group.
Rename the group Employee Details.
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 Employee Details.

The Employee Details page is now complete.

Creating the Introduction page

Pam knows that a good report includes an introduction on how to use it. To create an

Introduction page, do the following:

Right-click the Template page and choose Duplicate Page.
Rename the Duplicate of Template page to Introduction.
Click on the Introduction page’s tab and drag this page in between the

Template page and the Executive Summary page.
Click on the Insert tab of the ribbon and choose Text box from the

Elements section.
Use the Format text box pane to toggle Background to Off.
Resize this text box so that it’s the full size of the light gray area.
Enter text into the text box explaining the report.

The Introduction page should look similar to the following:

Figure 7.17 – The Introduction page

We are now done creating the pages of our report. Now, it is time to perform some finishing touches.

Finishing up

Even though Pam has finished building her report, she still needs to test the report and clean up some loose ends.

In the following subsections, we will perform some final testing and cleanup on the report to ensure that everything works correctly and that the report is ready for business users to consume.

Testing

Pam decides to work through an example scenario to see how the report performs. To perform this test, do the following:

Start on the Executive Summary page. Here, we can see that the overall utilization is good and above our goal. All of the branches are above our target utilization. However, one Division, in particular, is noticeably below the average. This is the 3001 Management division. Let’s investigate this Division further to see what is driving down utilization.
In the % Utilization by Division visual, right-click on the 3001 Management bar and choose Drill through and then Division Management. Checking the green dynamic title text, we can see that it correctly states 3001 Management.
We can see that our utilization for salaried employees is quite low and that utilization is particularly suffering in Nashville.
To investigate this further, right-click on the Nashville column in the % Utilization by Branch visual and choose Drill through and then Hours Detail. When checking our green dynamic title text, we are looking at resources for only 3001 Management and Nashville.
Click on the % column header to sort by ascending order.
It looks like the least utilized person, Irwin, Rachelle, was just recently hired. Her utilization is low but so are her total hours.
On the next line down, however, is Schaefer, Gustavo. This individual only has 3%

utilization but has logged almost 500 hours.
We want to check out how Schaefer, Gustavo is spending his time. Right-click on the row containing Schaefer, Gustavo and choose Drill through and then Employee Details.
In the % Hours by Category visual, we can see that this individual is mostly spending their time doing Sales Support.
We want to check out the next person on the list. Click the Back button (hold down the Ctrl key while clicking).
Our Hours Detail page is just like how we left it.
Right-click the row containing Beltran, Elvira and choose Drill through and then

Employee Details.
We can quickly see that this person has been taking a lot of PTO! Clicking on the first JobID code, we can see that over 72% of the 320 hours that were logged to this code are for PTO. This individual is already on the unlimited PTO plan!

Finally, Pam tests the Reset buttons. On the Employee Details page, hold down the

Ctrl key and then press the Reset button.
Do the same for the Hours Detail and Division Management pages.

Pam is satisfied that there is an intuitive flow to the report and that the report is in working order.

Cleaning up

Pam needs to do a little cleanup on the report before she can publish and share her hard work. To clean things up, do the following:

Hide the Template page by right-clicking the page tab and choosing Hide Page. An eyeball icon with a slash through it will appear next to the page’s name. As the report author, you can see this page, but the viewers of the report will not.
Pam can also hide unused tables within the report. Click on the Data view.
Right-click the Budgets and Forecasts table and choose Hide in report view.
Hide the MonthYears and Target Utilization tables as well.
Pam can also hide columns and measures. For example, the % Target Utilization

measure in the Calculations table is no longer used.
Expand the Calculations table, right-click % Target Utilization, and choose Hide in report view.

Finally, Pam clicks back to the Report view. On the View tab of the ribbon, she unchecks Gridlines and Snap to grid, and then checks Lock objects.

Now that Pam is done authoring the report, the positioning aids are no longer required, and locking the positions of the objects on the pages prevents visuals from accidentally being moved or resized.

Summary

In this chapter, we learned about the importance of proper planning when creating a final report. Planning a report by thinking about how users will use and interact with the report will make the report creation process go more smoothly and help ensure that users find the report intuitive and easy to use. Thinking ahead about using sync slicers

and report filters can save time and effort. Using a theme ensures that colors are consistent throughout the report and can also save time by reducing the amount of formatting that’s required for visuals. Creating a page template can also save time and effort when creating multiple report pages.

While creating the various pages of the utilization report, we learned valuable lessons about how to overcome the limitations of certain visuals by using DAX, as well as how to use DAX to create dynamic titles that are helpful when working with the drill-through feature and how to add visual cues by using SVG and data categorization. Finally, we learned how we can clean up certain report and data model elements by hiding them from report viewers, making the report cleaner and more user-friendly.

Now that we have created the final report, the next step is to publish and share our report with others. In the next chapter, we will introduce the Power BI service and demonstrate how to publish and share the report.

Questions

As an activity, try to answer the following questions on your own:

What Power BI feature allows the report author to preset elements such as colors and font sizes?
What are the three preparation steps that are taken before creating the final report pages?
What feature keeps the settings for slicers consistent across pages?
What type of visual can display the current value of a metric and the trend of that metric and track that metric against a goal?
What two features of Power BI are used to allow report viewers to reset the slicers on a page?
What DAX function can be used to determine whether a column has a single value?
What Power BI feature allows ad hoc information to be entered into the data model?
What two analytics features were used on the report pages?
What report and data elements can be hidden from report viewers?

Our New E-Store -> AnyTechKey.com

Learn Power BI Creating the Branch Management page

Creating the Branch Management page

Creating the Hours Detail page

Creating the Employee Details page

Creating the Introduction page

Finishing up

Testing

Cleaning up

Summary

Questions

Further reading

Leave a Comment Cancel Reply

Download books & sOFTWARE

Contact us
civilmdcgroup@gmail.com

Creating the Branch Management page

Creating the Hours Detail page

Creating the Employee Details page

Creating the Introduction page

Finishing up

Testing

Cleaning up

Summary

Questions

Further reading

Related Posts

Leave a Comment Cancel Reply

Download books & sOFTWARE

Contact uscivilmdcgroup@gmail.com

Contact us
civilmdcgroup@gmail.com