Skip to content

9. Power BI Report

Jump to bottom
Silvia Aragon edited this page Jun 9, 2024 · 16 revisions

The first step is to create all report pages. Go to Report View and add four pages, as follows:

  • Executive Summary
  • Customer Detail
  • Product Detail
  • Stores Map

1. Executive Summary Page

executive-summary

The executive summary contains:

  • Card visuals for Total Revenue, Total Orders and Total Profit.
  • Line chart to show the revenue over time and the predicted revenue in the coming cycles.
    • Drill-down: Set X axis to your Date Hierarchy, with only the Start of Year, Start of Quarter and Start of Month levels displayed.
    • Set Y-axis to Total Revenue.
    • Predictive modelling: Add a trend line, and a forecast for the next 10 periods with a 95% confidence interval.
  • KPI charts for quarterlyy revenue, profit and orders:
    • The Value field should be Total Revenue/Profit/Orders
    • The Trend Axis should be Start of Quarter
    • The Target should be Target Revenue/Profit/Orders.
    • In the Format pane, set the Trend Axis to On, expand the associated tab, and set the values as follows: Direction -> High is Good | Bad Colour -> red | Transparency -> 15%.
  • Donut charts to show the breakdown of revenue both by country and by store type.
    • X-axis: Total Revenue measure.
    • Filter with Store[Country] column or Store[Store Type] column.
  • Clustered bar chart to show the number of orders by product category.
    • X-axis: Total Orders measure.
    • Filter with Products[Category] column.

1.1. Conditional formatting in bar/column charts using DAX measures

I want my column and bar charts to be coloured according to the bar/column values. In this case, I want the category with the largest value (in the executive summary page, the product category with most orders) to have a purple bar, while the rest of the categories should be grey. To achieve this in a way that will be responsive to any report filtering, we need to use DAX measure. This process will be applied to the bar chart in the executive summary page, as well as to the column chart in the customer detail page. The measures shown below have been used to customise the chart in the executive summary, but similar measures were used for the customers chart.

  1. Create a measure that calculates the number of orders per category.
_TotalOrdersPerCategory = CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Products, Products[Category]))
  1. Create a measure that returns the maximum value in the first measure.
_MaxOrdersPerCategory = 
VAR MaxValue =
MAXX(ALLSELECTED(Products[Category]), [_TotalOrdersPerCategory]) 
RETURN
IF(MaxValue = [_TotalOrdersPerCategory], MaxValue, BLANK())
  1. Create conditional measure that evaluates whether each category is the maximum or not, and applies colour accordingly.
_ColourOrdersBarChart = 
VAR MaxValue = [_MaxOrdersPerCategory]
RETURN
SWITCH(
    TRUE(),
    [_TotalOrdersPerCategory] = MaxValue, "#8E65F4",
    "#aaaaaa")

In the visual's Format pane, we need to go to Bars (or Columns) > Color > fx. Format style as Field value and then selected the measure created above: _ColourOrdersBarChart.

1.2. Adding a fields parameter & slicer for increased interactivity

I decided to create a fields parameter with the main KPIs (as shown below) to add more interactivity to the time-series trending charts in my report. To add a fields parameter, we need to go to the Modelling pane and select Parameter > Fields. The fields parameter can then be created as shown below:

Metric_Selection_Field_parameter

As a result, a new table will appear in our data model, as well as a slicer in our report. To connect this slicer to a specific visual, we need to modify the visual itself. To do this, we need to click on the visual, go to its Format > Fields pane, and replace the original measure in the y-axis with the fields parameter just created. In this case, the parameter is called Metric Selection:

Metric_Selection_Slicer

This process was later repeated for the area chart in the Customer Detail page.

2. Customer Detail Page

customer-detail

The customer detail page contains:

  • Headline card visuals:
    • Unique customers card: measure Total Customers.
    • Revenue per customer card: measure Revenue per Customer.
  • Donut charts to show the breakdown of total customers by country.
    • X-axis: Total Customers measure.
    • Filter with Users[Country] column.
  • Column chart to show the number of customers by product category.
    • X-axis: Total Customers measure.
    • Filter with Products[Category] column.
  • Line chart to show the customer trending over time and the predicted customer numbers in the coming cycles.
    • Drill-down: Set X axis to your Date Hierarchy, with only the Start of Year, Start of Quarter and Start of Month levels displayed.
    • Set Y-axis to Total Customers.
    • Predictive modelling: Add a trend line, and a forecast for the next 10 periods with a 95% confidence interval.
  • Top 20 Customers table. Built using measures Total Revenue and Total Orders filtered by Customer[Full Name] column.
  • Top Customer card visuals:
    • Top Customer card: Top Customer Name measure.
    • Revenue card: Total Revenue by Top Customer measure.
    • Order card: Orders By Top Customer measure.
  • Date slicer. Add slicer > style: 'between'. Select Dates[Year].

3. Product Detail Page

Product Detail

The customer detail page contains:

  • Filter state cards:
    • First define the following measures:
    Category Selection = IF(ISFILTERED(Products[Category]), SELECTEDVALUE(Products[Category], "No Selection")

Country Selection = IF(ISFILTERED(Stores[Country]), SELECTEDVALUE(Stores[Country], "No Selection")
    • Add each measure to a filter card visual. These measures will work in tandem with the slicer toolbar we will build below.
  • Gauge visuals. Should show the current-quarter performance of Orders, Revenue and Profit against a quarterly target (10% quarter-on-quarter growth).
    • As the value, assign the measures QTD Revenue/Orders/Profit (QTD = quarter to date), respectively.
    • Set the target measures as the maximum value of the guage.
  • Area chart of revenue by product category.
    • X axis should be Dates[Start of Quarter].
    • Y axis values should be Total Revenue measure.
    • Legend should be Products[Category].
  • Scatter graph. Which product ranges are both top-selling items and also profitable?
    • Create a new calculated column called [Profit per Item] in the Products table.
    • Values: Products[Description]
    • X-Axis: Products[Profit per Item]
    • Y-Axis: Orders[Total Quantity]
    • Legend: Products[Category]
  • Top Products table. Built in the same way as the table from the customer Detail page.
  • Top Product card visuals:
    • Top Customer card: Top Customer Name measure.
    • Revenue card: Total Revenue by Top Customer measure.
    • Order card: Orders By Top Customer measure.

3.1. Adding colour sentimentality to Gauge visuals using DAX measures

For my gauge visuals, I would like to add sentimentality to the callout value only: it should show in green if the target has been met, and in red otherwise. To achieve this, we need to first create a DAX measure:

Gauge Value Colour Orders = 
SWITCH(
    TRUE(),
    [Total Orders] < [Target 10 Quarter Orders], "#D82C20",
    [Total Orders] >= [Target 10 Quarter Orders], "#05970D"
)

In the visual's Format pane, we need to go to Callout Value > Color > fx and select the options shown in the image below.

gauge_sentimentality_howto2

This process was repeated in the same way for all other gauge visuals in the report, including the map's tooltip page.

3.2. Slicer Toolbar: Filtering by Category and Country

We want to create a pop-out toolbar to filter our Poduct Detail page by product category and country. Upon clicking on the top left Filter button, a slicer opens, letting up choose product category and country:

product_slicer_3

To create it, we need to follow these steps:

  1. Adds filter button to the top of the navigation bar, with the following settings:
product_slicer_2
  1. Draw a new rectangle shape and add vertical list slicers:
    • One set to Products[Category].
    • The second set to Stores[Country].
  2. Add a back button to the slicer panel (select 'Back' button type).
  3. Open the Bookmarks pane and add two new bookmarks: one with the toolbar group hidden in the Selection pane, and one with it visible. Name them Slicer Bar Closed and Slicer Bar Open.
product_slicer_1 product_slicer_3

Right-click each bookmark in turn, and ensure that Data is unchecked.

slice_bar_settings

4. Stores Map Page

Contains a map visual with all store locations, as well as a country slicer that allows the user to zoom in to a specific country in the map. The final map visual shown below is an Azure map visual, but a normal map visual can also be used in the same way. However, normal map visuals will go obsolete in favour of Azure map visuals in the near future, according to Microsoft.

stores-map

Fields: To add the map visual, set the following values:

  • Location: Geography hierarchy
  • Bubble size: Profit YTD measure.
map_settings

4.1. Creating a Stores Tooltip Page

To allow users to be able to see each store's year-to-date profit performance against the profit target just by hovering the mouse over a store on the map.

Upon hovering on a specific location, the map shows a gauge visual of Profit YTD vs. target. This is achieved by creating a separate page in the report (called Store ToolTip) and linking it to map under the Tooltips field (see map settings picture in main section).

tooltip_final

4.2. Creating a Stores Drillthrough Page

To make it easy for the region managers to check on the progress of a given store, we need to create a drillthrough page that summarises each store's performance.

Stores DrillThrough

This page should open once the user click on a specific store on the map.

map_drilltrhough

This drillthrough page should be created as a new page in the report, called Stores Drillthrough. Once created, open the format pane and expand the Page information tab. Set the Page type to Drillthrough and set Drill through when to Used as category. Set the 'Drill through from' field to [Country Region].

drilltrhough_settings

5. Crossfiltering

To avoid unnecessary confusion and get the right message across, it is important that only the desired visuals can filter the rest. Power BI lets us control these cross-filtering interactions.

From the Edit Interactions view in the Format tab of the ribbon, we can modify filtering interactions between visuals:

Executive Summary Page

  • Product Category bar chart and Top 10 Products table should not filter the card visuals or KPIs
  • Top 10 Products table should not affect any other visuals

Customer Detail Page

  • Top 20 Customers table should not filter any of the other visuals
  • Total Customers by Product Donut Chart should not affect the Customers line graph
  • Total Customers by Country donut chart should cross-filter Total Customers by Product donut Chart

Product Detail Page

  • Orders vs. Profitability scatter graph should not affect any other visuals

6. Navigation Bar

A navigation bar was set up on the left side of each page, to allow navigation between pages of the report. To allow this functionality, we need to Add Button and turn Action to ON, select Navigation and the desired report page for each button.

Clone this wiki locally