Learn Power BI Creating the Final Report

Creating the Final Report

We have spent the last few chapters importing, scrubbing, and shaping data, and then carefully crafting it into a data model. In addition, we have learned about the many powerful features of Power BI that can help us unlock insights into our data.

All of this hard work is about to pay off. In this chapter, we will put all of these tools and techniques together into a final report that we can share with the organization! This chapter provides detailed steps regarding how to create a polished report. This will not

only provide ease of use and valuable insights to the viewer but can also be easily created and maintained over time.

In this chapter, we will cover the following topics:

• Preparing the final report
• Creating the final report pages
• Finishing up

Technical requirements

You will need to meet the following requirements to follow the instructions in this chapter:

• An internet connection.

• Microsoft Power BI Desktop.
  • Download LearnPowerBI_CH7Start.pbix from GitHub at https://github. com/PacktPublishing/Learn-Power-BI-second-edition/tree/
    main/Chapter07.
    
• Check out the following video to see the Code in Action: https://bit. ly/3wvTk1i

Preparing the final report

Before we start creating the final version of our report, it is important to take a moment to get things in order concerning how the report will be created. A little bit of preparation can save a lot of time, headaches, and avoid rework. So, we’ll start preparing for the final report in the following subsections.

Planning the final report

Continuing with the example scenario we introduced in Chapter 2, Planning Projects with Power BI, Pam takes a moment to contemplate the various groups of individuals within the company that the final utilization report will serve.

The main groups include the following:

• C-level executives
• Division managers
• Branch managers

Each group is interested in utilization but in slightly different ways. The C-level executives simply want a quick summary of the utilization numbers, whether it is meeting the desired goals, how utilization is trending, and so on. Division managers, on the other hand, will likely want to drill a little bit deeper and understand how utilization looks on individual projects and who the top and bottom individual contributors on those projects are. Branch managers will be interested in the same sort of information as division managers but only within their locations.

Pam decides that her final report will include six pages:

• Executive Summary: A page summarizing the information at a high level, including relevant trends.
• Division Management: A summary page focused on division managers.
  
• Branch Management: A summary page focused on branch managers.
  
• Hours Detail: A page that includes a detailed hours breakdown.
• Employee Details: A page focused on breaking down the utilization of particular employees.
• Introduction: An introduction to the report and how to use it.

Now that we have the overall plan for the report, it is time to clean up some of our prior work.

Cleaning up

Before Pam begins working on the final report, she wants to clean up some of the pages within her current working draft. To do this, follow these steps:

• Delete all of the pages in the report that have been created thus far except for Page 4. This can be done by hovering over a page tab and clicking the small x icon or by right-clicking a page tab and selecting Delete Page.
• While on Page 4, delete the Key influencers visualization and any other visualizations.
  

With that, you’re all done cleaning up! Now, let’s tackle applying a theme to our report.

Using a theme

Pam wants all of the pages of her report to be consistent and use corporate branding guidelines. To this end, she has created a theme file for use with Power BI Desktop. This theme file contains all of the approved corporate branding colors and standard, larger font size settings. This theme will save Pam a lot of time and effort when it comes to formatting the final report, and the managers will appreciate the corporate branding as opposed to the default colors in Power BI Desktop, which, frankly, Pam is not very fond of.

The theme file is simply a text file that is named LearnPowerBI.json. This is a

JavaScript Object Notation (JSON) file. Its content is as follows:

{     

    “name”: “LearnPowerBI”,     

    “dataColors”:     

    [     

    “#EC670F”,     

    “#3C3C3B”,     

    “#E5EAEE”,     

    “#5C85E6”,     

    “#29BCC0”,     

    “#7EAE40”,     

    “#20B2E7”,     

    “#D3DCE0”,     

    ],     

    “background”:”#EC670F”,     

    “foreground”: “#FFFFFF”,     

    “tableAccent”: “#29BCC0”,     

    “visualStyles”:     

    {“*”:     

    {“*”:     

    {“*”:     

    [{     

    “fontSize”:14,     

    “fontFamily”:”Segoe UI”,     

    “color”:{“solid”:{}}     

    }],     

    “general”:[{“responsive”:true}]     

    }     

    },     

    “tableEx”:     

{“*”:     

    {     

    “columnHeaders”:     

    [{     

    “autoSizeColumnWidth”:true,     

    “fontFamily”:”Segoe (Bold)”,     

    “fontSize”:14     

    }]     

    }     

    }     

    }     

}     

This theme file can be downloaded from GitHub here: https://github.com/ PacktPublishing/Learn-Power-BI-second-edition.

To use this theme, perform the following steps:

• In Power BI Desktop, while in the Report view, click on the View tab. Then, in the Themes section, click the drop-down arrow and choose Browse for themes, as shown in the following screenshot:

Figure 7.1 – Browse for themes in Power BI Desktop

• Navigate to the LearnPowerBI.json file, select it, and click the Open button.
  

  An Import theme dialog will appear, stating whether the theme file was imported successfully or not.

  • Click the Close button.
    

Now that we have applied a theme to our report, our next step is to create a template for the final pages of the report.

Creating a page template

Pam decides that she wants all of her pages to have the same basic layout, look, and feel. Therefore, she decides that, as her next step in creating her final report, she will create a page template that can be used as the basis for all of the pages in her report. This will take some initial work to complete but will save a tremendous amount of time later on as pages are added to the report. Think of the value of a template page this way: instead of setting up the layout and format for each page of a report, we do these operations once instead.

The Template page should look as follows when we are done:

Figure 7.2 – Completed Template page

If you want to skip creating this template, download the LearnPowerBI_ Ch7TemplateOnly.zip file from https://github.com/PacktPublishing/ Learn-Power-BI-second-edition. Otherwise, complete the following steps to create the page template:

• Rename Page 4 Template.
  
• Next, set the background color for the page by using the Format sub-pane of the Visualizations pane, expanding Page background, and clicking the drop-down arrow under the Color setting.
  
• Select the last color in the top row under Theme colors. The tip for this color is

  #D3DCE0, Theme color 8.
  

• Set Transparency to 0%.
  

To aid her in laying out the visuals on her pages, Pam then decides to activate positioning and layout tools by clicking on the ribbon’s View tab. In the Page Options section, she checks the boxes next to Gridlines and Snap to grid.

Pam has decided that the right-hand side of each page will be reserved for slicers that the end users can use to create various views of the visualizations on the page focused on their desired information. To mark this area off as separate, Pam decides to create a rectangle to mark where she will place the slicers.

To do this, follow these steps:

• Select the Insert tab of the ribbon and in the Elements section, select Shapes and then Rectangle. Note that white dots appear on the canvas, marking the gridlines.
• Position the rectangle in the top right of the page canvas.
• Next, adjust the size of the rectangle so that it takes up the entire length of the page and the first three vertical grid lines from the right-hand side of the page.
• With the rectangle selected, expand the Fill section of the Format shape pane, and change the Fill color to the fourth color in the top row from the left, #3C3C3B, Theme color 2.
  
• Expand the Outline section and change Outline color to this same color.
  
• Finally, expand the Shape section and adjust Round edges to 10 px. Pam decides that she should title this area. To do so, follow these steps:
  • From the Insert tab of the ribbon, choose Text box from the Elements section.
    
  • Place this visual at the top of the rectangle and resize it so that the Text box is the entire width of the rectangle and consumes four grid dots of vertical height.

• Type CONTROLS into the text box.
  
• Double-click the word CONTROLS and change its font to Segoe (Bold).
  
• Increase the font size to 24 and Center the text.
  
• Change the font color to #E5EAEE, Theme color 3.
  
• In the Format text box pane, toggle Background to Off.
  

  The final position of the text box can be checked in the General section of the

  Format text box pane and adjusted if necessary:
  

  • X Position: 1056
    
  • Y Position: 0
    
  • Width: 224
    
  • Height: 64
    

  Your page should look similar to the following:

Figure 7.3 – CONTROLS text box position

Next, Pam wants to show a relative date slicer at the top of this rectangle. Follow these steps to add the slicer:

• Click on a blank area of the canvas, expand the Calendar table in the Fields pane, and select Date.
  
• In the Visualizations pane, click the drop-down arrow for Date in the Values field well and switch this to Date instead of Date Hierarchy.
  
• Switch this new visualization to a Slicer using the Visualizations pane.
  
• Shrink this visual to the minimum vertical size where Date still appears at the top of the slicer and then move this slicer so that it is centered directly underneath the CONTROLS text box. Red guidelines will appear to aid in positioning.
• Adjust the horizontal size as necessary so that it fits within the rectangle.
• In the Format sub-pane of the Visualizations pane, toggle Background to Off
  
• Expand the Date inputs section and change Font color to #E5EAEE, Theme color 3.
  
• Expand the Slicer header section and change Font color to #E5EAEE, Theme color 3, Title text to Dates, Text size to 14, and Font family to Segoe UI (Bold).
  
• Finally, adjust the vertical size of this slicer until the word Dates increases to its proper size and the yellow warning disappears from the Slicer header area.

  The slicer should have the following sizes and coordinates, which you can check in the General section of the Format sub-pane:

  • X Position: 1056
    
  • Y Position: 64
    
  • Width: 224
    
  • Height: 160
    

Your visuals should look similar to the following:

Figure 7.4 – CONTROLS text box and Dates slicer

Next, Pam wants to use the Target Utilization slicer she created earlier. To add this slicer to the page, perform the following steps:

• Click on a blank area of the canvas, expand the Target Utilization table, and click the check box next to the Target Utilization column.
  
• Resize this slicer and position it underneath the Dates slicer. It is okay if the visuals overlap.
• In the Format sub-pane of the Visualizations pane, toggle Background to Off.
  
• Expand the Numeric inputs section and change the Font color property of
  

  #E5EAEE, Theme color 3.
  

• Format Slicer header to have a Font color of #E5EAEE, Theme color 3, a Text size
  

  of 14, and a Font family of Segoe UI (Bold).
  

• Finally, adjust the vertical size of this slicer until the word Target Utilization increases to its proper size and the yellow warning disappears from the Slicer header area.

  The slicer should have the following sizes and coordinates, which you can check in the General section of the Format sub-pane:

  • X Position: 1056
    
  • Y Position: 176
    
  • Width: 224
    
  • Height: 160
    

  The visuals on your page should look similar to the following:

Figure 7.5 – Target Utilization slicer added

Pam also wants a slicer for branches. To create this, do the following:

• Click on a blank area of the canvas and then expand the People table in the Fields

  pane and choose the Location column.
  

• Switch this visual to a Slicer as well.
• Shrink this slicer vertically and reposition this slicer underneath the Target Utilization slicer. It is okay if the visuals overlap.
• Adjust the horizontal size as necessary so that it fits within the rectangle.
• In the Format sub-pane of the Visualizations pane, toggle Background to Off.
  
• In the Format sub-pane of the Visualizations pane, expand the Selection controls
  

  section and toggle Show “Select All” option to On.
  

• Expand the Slicer header section and change Title text to Branch, Font color to
  

  #E5EAEE, Theme color 3, Text size to 14, and Font family to Segoui UI Bold.
  

• Expand the Items section and change Font color to #E5EAEE, Theme color 3, Background to #3C3C3B, Theme color 2, and Text size to 12.
  
• Hover over the slicer with your mouse, click the down arrow icon at the top right, and choose Dropdown.

  This slicer should have the following General properties:

  • X Position: 1056
    
  • Y Position: 288
    
  • Width: 224
    
  • Height: 80
    

The visuals on your page should look similar to the following:

Figure 7.6 – Branch slicer added

 

To create a Division slicer, perform the following steps:

• Select the Branch slicer and use Ctrl + C and Ctrl + V to copy and paste the slicer, respectively.
  
• Reposition this slicer directly underneath the original Branch slicer.
• Replace the slicer’s Field with the Division column (not Division (groups) or
  

  Division Hierarchy) from the Hours table.
  

• In the Format sub-pane of the Visualizations pane, expand the Slicer header
  

  section and change Title text to Division.
  

  This slicer should have the following General properties:

  • X Position: 1056
    
  • Y Position: 368
    
  • Width: 224
    
  • Height: 80
    

  The Division slicer should be positioned directly underneath the Branch slicer, as shown in the following screenshot:

Figure 7.7 – The Division slicer underneath the Branch slicer

To create a slicer based on the type of employee, follow these steps:

• Select the Division slicer and use Ctrl + C and Ctrl + V to copy and paste the slicer, respectively.
  
• Position this slicer directly underneath the original Division slicer.
• Replace this slicer’s Field with the EmployeeType column (not Employee Type (groups)) from the People table.
  
• In the Format sub-pane of the Visualizations pane, expand the Slicer header
  

  section and change Title text to Employee Type.

This slicer should have the following General properties:

• X Position: 1056
  
• Y Position: 448
  
• Width: 224
  
• Height: 80
  

The Employee Type slicer should be positioned directly underneath the Division

slicer, as shown in the following screenshot:

Figure 7.8 – The Employee Type slicer beneath the Division slicer

These are the common slicers that Pam envisions on each page. Pam also plans on creating a bookmark for each page that will reset all of the slicers and filters on the page. To plan for this, follow these steps:

• Click on a blank area of the canvas. Then, on the Insert tab of the ribbon, choose

  Buttons and then Reset.
  

• Shrink this button to the smallest horizontal width where the entire icon can be seen and reposition this button in the lower right-hand corner of the page.
• With the button selected, toggle Background to Off in the Format button pane.
  
• Also, toggle Button Text to On in the Format button pane.
  
• Expand the Button Text section and change the top dropdown from Default state
  

  to On hover.
  

• Enter RESET for Button Text.
  
• Change Font color to #E5EAEE, Theme color 3, Font family to Segoe UI (Bold), and Horizontal alignment to Center.
  

Pam also plans on using drill through on some pages. To prepare for this, do the following:

• Copy and paste the reset button and reposition it at the bottom left of the rectangle.
• In the Format button pane, expand the Icon section and change Shape from Reset
  

  to Back

• Expand the Button Text section, change the state to On hover, and replace the word RESET with BACK.
  
• Toggle Action to On and ensure that Type is set to Back.
  

Finally, Pam wants to brand all of the report pages with the corporate logo. To add the logo, follow these steps:

• Click on a blank area of the canvas, click the Insert tab of the ribbon, and select

  Image from the Elements section.
  

• Select Packtlogo.png. This file can be downloaded from GitHub at https:// github.com/PacktPublishing/Learn-Power-BI-second-edition.
  
• Shrink this image as necessary and place the image just above the buttons. The corporate logo and buttons are shown in the following screenshot:

Figure 7.9 – Corporate logo and buttons

The completed Template page should now look like what was shown in Figure 7.2.