Design Power BI reports

Lab story

In this lab, you’ll create a three-page report. You’ll then publish it to the Power BI service, where you’ll open and interact with the report.

In this lab, you learn how to:

  • Design a report.
  • Configure visual fields and format properties.
  • Sync slicers.
  • Publish the report to the Power BI service.
  • Interact with a report and its visuals.

This lab should take approximately 45 minutes.

Get started

To complete this exercise, first open a web browser and enter the following URL to download a zip file:

https://github.com/MicrosoftLearning/PL-300-Microsoft-Power-BI-Data-Analyst/raw/Main/Allfiles/Labs/08-design-power-bi-reports/08-design-report.zip

Extract the file to the C:\Users\Student\Downloads\08-design-report folder.

Open the 08-Starter-Sales Analysis.pbix file.

Note: You can dismiss the sign-in by selecting Cancel. Close any other informational windows. Select Apply Later, if prompted to apply changes.

Design page 1

In this exercise, you’ll design the first report page. When you’ve completed the design, the page will look like the following:

Screenshot of page 1, comprising a logo, two slicers, and three visuals.

  1. In Power BI Desktop, to rename the page, at the bottom-left, right-click Page 1, then select Rename. Rename the page as Overview.

    Tip: You can also double-click the page name to rename it.

  2. To add an image, on the Insert ribbon tab, from inside the Elements group, select Image.

    Picture 1

  3. In the Open window, navigate to the C:\Users\Student\Downloads\08-design-report folder.

  4. Select the AdventureWorksLogo.jpg file, and then select Open.

  5. Drag the image to position it at the top-left corner, and also drag the guide markers to resize it.

    Picture 2

  6. To add a slicer, first de-select the image by selecting an empty area of the report page, then select Slicer in the Visualizations pane.

    Picture 3

  7. In the Data pane, drag the Date | Year field (not the Year level of the hierarchy) into the slicer Field well in the Visualizations pane.

    The labs use a shorthand notation to reference a field. It will look like this: Date | Year. In this example, Date is the table name and Year is the field name.

  8. To convert the slicer from a list to a dropdown, in the Visualizations pane, select Format your visual. Expand Slicer settings, and then set the Style dropdown to Dropdown.

    Picture 4

  9. Resize and position the slicer so it sits beneath the image and is the same width as the image.

    Picture 5

  10. In the Year slicer, open the dropdown list, select FY2020, and then collapse the dropdown list.

    The report page is now filtered by year FY2020.

    Picture 6

  11. De-select the slicer by selecting an empty area of the report page.

  12. Create a second slicer, based on the Region | Region field (not the Region level of the hierarch).

  13. Leave the slicer as a list, and then resize and position the slicer beneath the Year slicer.

    Picture 7

  14. De-select the slicer by selecting an empty area of the report page.

  15. To add a chart to the page, in the Visualizations pane, select the Line and Stacked Column Chart visual type.

    Picture 8

  16. Resize and position the visual so it sits to the right of the logo, and so it fills the width of the report page.

    Picture 9

  17. Drag and drop the following fields into the visual:

    • Date | Month
    • Sales | Sales
  18. In the visual fields pane (located in the Visualizations pane), notice that the fields are assigned to the X-axis and Column y-axis wells.

    By dragging fields into a visual, they’re added to default wells. For precision, you can drag fields directly into the wells, as you’ll do next.

    Picture 10

  19. From the Data pane, drag the Sales | Profit Margin field into the Line y-axis well.

    Picture 11

  20. Notice that the visual only has 11 months.

    The last month of the year, 2020 June, doesn’t have any sales yet. By default, the visual has eliminated months with BLANK sales. You’ll now configure the visual to show all months.

  21. In the visual fields pane, in the X-axis well, for the Month field, select the down-arrow, and then select Show items with no data.

    Picture 12

    Notice that the month 2020 June now appears.

  22. De-select the chart by selecting an empty area of the report page.

  23. To add a chart to the page, in the Visualizations pane, select the Stacked Column Chart visual type.

    Picture 13

  24. Resize and position the visual so it sits beneath the column/line chart, and so it fills half the width of the chart above.

    Picture 14

  25. Add the following fields to the visual wells:

    • X-axis: Region | Group
    • Y-axis: Sales | Sales
    • Legend: Product | Category
  26. De-select the chart by selecting an empty area of the report page.

  27. To add a chart to the page, in the Visualizations pane, select the Stacked Bar Chart visual type.

    Picture 15

  28. Resize and position the visual so it fills the remaining report page space.

    Picture 16

  29. Add the following fields to the visual wells:

    • Y-axis: Product | Category
    • X-axis: Sales | Quantity
  30. To format the visual, open the Format pane.

    Picture 17

  31. Expand the Bars section, and then in the Color group, set the Color property to a suitable color (to complement the column/line chart).

  32. Set the Data Labels section to On.

    Picture 18

  33. Save the Power BI Desktop file.

    The design of the first page is now complete.

Design page 2

In this exercise, you’ll design the second report page. When you’ve completed the design, the page will look like the following:

Screenshot of page 2, comprising a slicer and matrix.

Important: When detailed instructions have already been provided in the labs, the lab steps will provide more concise instructions. If you need the detailed instructions, you can refer back to other tasks in this lab.

  1. To create a new page, at the bottom-left, select the plus icon. When the page is added, rename it as Profit.

  2. Add a slicer based on the Region | Region field.

  3. Use the Format pane to show the Select all option (located in the Slicer settings > Selection section).

  4. Resize and position the slicer so it sits at the left side of the report page, and so it’s about half the page height.

    Picture 19

  5. Add a matrix visual, and resize and position it so it fills the remaining space of the report page

    Picture 20

  6. Add the Date | Fiscal hierarchy to the matrix Rows well.

    Picture 21

  7. Add the following five Sales table fields to the Values well:

    • Orders (from the Counts folder)
    • Sales
    • Cost
    • Profit (from the Pricing folder)
    • Profit Margin (from the Pricing folder)

    Picture 22

  8. In the Filters pane (located at the left of the Visualizations pane), notice the Filters on this page section (you may need to expand open the pane and scroll down).

    Picture 23

  9. From the Data pane, drag the Product | Category field into the Filters on this page well.

    Fields added to the Filters pane can achieve the same result as a slicer. One difference is they don’t take up space on the report page. Another difference is that they can be configured to achieve more sophisticated filtering requirements.

  10. Inside the filter card, at the top right, select the arrow to collapse the card.

  11. Add each of the following Product table fields to the Filters on this page well, collapsing each, directly beneath the Category field:

    • Subcategory
    • Product
    • Color

    Picture 24

  12. Save the Power BI Desktop file.

    The design of the second page is now complete.

Design page 3

In this exercise, you’ll design the third—and final—report page. When you’ve completed the design, the page will look like the following:

Screenshot of page 3, comprising a slicer and three visuals.

  1. Create a new page and rename it as My Performance.

  2. To simulate the performance of row-level security filters, drag the Salesperson (Performance) | Salesperson field to the page level filters in the filter pane.

    Picture 25

  3. In the filter card, select Michael Blythe.

    Data on the report page will now be filtered to display data for Michael Blythe only.

  4. Add a dropdown slicer based on the Date | Year field, and then resize and position it so it sits at the top-left corner of the page.

    Picture 26

  5. In the slicer, set the page to filter by FY2019.

    Picture 27

  6. Add a Multi-row Card visual to the page, and then resize and reposition it so it sits to the right of the slicer and fills the remaining width of the page.

    Picture 28

    Picture 29

  7. Add the following four fields to the visual:

    • Sales | Sales
    • Targets | Target
    • Targets | Variance
    • Targets | Variance Margin
  8. Format the visual:

    • In the Callout values section, increase the font size property to 28pt.
    • In the General tab, in the Effects section, set the background color property to a light gray color (such as White, 10% Darker) to provide contrast.

      Picture 30

  9. Add a Clustered Bar Chart visual to the page, and then resize and position it so it sits beneath the multi-row card visual and fills the remaining height of the page, and half the width of the multi-row card visual.

    Picture 31

    Picture 32

  10. Add the following fields to the visual wells:

    • Y-axis: Date | Month
    • X-axis: Sales | Sales and Targets | Target

      Picture 33

  11. To create a copy of the visual, press Ctrl+C, and then press Ctrl+V.

  12. Position the new visual to the right of the original visual.

    Picture 34

  13. To modify the visualization type, in the Visualizations pane, select Clustered Column Chart.

    Picture 35

    It’s now possible to see the same data expressed by two different visualization types. The design of the final page is complete.

Sync slicers

In this task, you’ll sync the Year and Region slicers.

  1. On the Overview page, set the Year slicer to FY2018.

  2. Go to the My Performance page, and then notice that the Year slicer has a different value.

    When slicers aren’t synced, it can contribute to misrepresentation of data and frustration for report users. You’ll now sync the report slicers.

  3. Return to the Overview page, and then select the Year slicer.

  4. On the View ribbon tab, from inside the Show Panes group, select Sync Slicers.

    Picture 36

  5. In the Sync Slicers pane (at the left of the Visualizations pane), in the second column (which represents syncing), check the checkboxes for the Overview and My Performance pages.

    Picture 37

  6. On the Overview page, select the Region slicer.

  7. Sync the slicer with the Overview and Profit pages.

    Picture 38

  8. Test the sync slicers by selecting different filter options, and then verifying that the synced slicers filter by the same selection.

  9. To close the Sync Slicer pane, select the Sync Slicer option located on the View ribbon tab.

Publish and explore the report

In this exercise, you’ll publish the report to the Power BI service. You will then explore the published report behavior.

Note: You can review the remainder of the exercise, even if you don’t have access to the Power BI service to perform the tasks directly.

  1. Select the Overview page, then save the Power BI Desktop file.

  2. On the Home ribbon tab, from inside the Share group, select Publish.

    If you’re not signed in to Power BI Desktop already, you’ll need to first sign in before you publish the report.

    Picture 39

  3. In the Publish to Power BI window, notice that My workspace is selected.

    This lab doesn’t go into detail about the different items within the Power BI service.

  4. To publish the report, select Select. Wait until the publication completes.

  5. When the publication succeeds, select Got It.

  6. Open the Microsoft Edge browser, then sign in at https://app.powerbi.com.

  7. In the browser window, in the Power BI service, in the Navigation pane (located at the left, and it could be collapsed), expand My Workspace.

    Picture 40

  8. Review the contents of the workspace.

    • There are different types of items that can exist in a workspace, but what’s relevant to this lab are semantic models and reports.
    • You might need to refresh the browser if the semantic model isn’t visible.
    • When you published the report, the data model was published as a semantic model.
  9. To explore the report, select the 08-Starter-Sales Analysis report.

  10. At the left, in the Pages pane, notice that the Overview page is selected.

  11. In the Region slicer, while pressing the Ctrl key, select multiple regions.

  12. In the column/line chart, select any month column to cross filter the page.

  13. While pressing the Ctrl key, select another month.

    By default, cross filtering filters all other visuals on the page.

  14. Notice that the bar chart is filtered and highlighted, with the bold portion of the bars representing the filtered months.

  15. Hover the cursor over the bar chart visual, and then at the top right, hover the cursor over the Filter icon.

    Picture 41

    The filter icon allows users to understand all filters that are applied to the visual, including slicers and cross filters from other visuals.

  16. Hover the cursor over a bar, and then notice the tooltip information.

  17. To undo the cross filter, in the column/line chart, select an empty area of the visual.

  18. Hover the cursor over the stacked column chart visual, and then at the top right, select the Focus mode icon.

    Focus mode zooms the visual to full page size.

    Picture 42

  19. Hover the cursor over different segments of the bar charts to reveal tooltips.

  20. To return to the report page, at the top-left, select Back to Report.

    Picture 43

  21. Hover the cursor over one of the visuals again, then at the top-right, select the ellipsis (…), and then notice the menu options. Try out each of the options, except the ones within Share.

    Picture 44

  22. At the left, in the Pages pane, select the Profit page.

    Picture 45

  23. Notice that the Region slicer has the same selection as made on the Overview page, thanks to synced slicers.

  24. In the Filters pane (located at the right), expand a filter card, and apply some filters.

    The Filters pane allows you to define more filters than could possibly fit on a page as slicers.

  25. In the matrix visual, use the plus (+) button to drill into the Fiscal hierarchy.

  26. Select the My Performance page.

    Picture 46

  27. At the top right on the menu bar, select View, and then select Full Screen.

    Picture 47

  28. Interact with the page by modifying the slicer, and cross filtering the page.

  29. At the bottom of the window, notice the commands to change page, navigate backwards or forwards between pages, or to exit full screen mode.

  30. Select the right icon to exit full screen mode.

    Picture 48

Lab complete