KPI Dashboards in Power BI part 1: Building the dashboard - KPI Cards

Introduction 

• Acts as a graphical presentation of a company's KPI's.
• Using visual graphs and charts to enable tracking of a company’s performance.
• Tailored to specific user requirements.
• Can be strategic, operational or individual.

Benefits of tracking KPI's in Power BI 

• Instead of trying to cope with and process large amounts of ad hoc, unstructured data you have one powerful enterprise data repository.
• Run automated data ETL (Extraction, Transformation & Load) processes for creating clean efficient and structured data. E.g. Using Power Query.

2.  All your KPI's are in one place and with a Power BI dashboard, you can  

• Compare and analyze all your KPIs in one place
• Identify where you are on your goals
• Make decisions to achieve those goals

3.  KPI's are updated in real time  

• KPI's can be set up to refresh in real time
• Eliminating the need to search though manual documents and endless data
• Enabling you to keep a step ahead and focus on value-added activities

4. KPI’s are interactive offering users rapid insights into data trends

• Power BI can offer bespoke functionality that you cannot get elsewhere
  • Current month / YTD filter
• Power BI is very good at showing trends:
  • Trend analysis in KPI cards
  • Variance analysis in sparklines
• Users are empowered to filter or drill-down into lower levels of the data
  

5.  Power BI KPI’s enable users to discover or predict issues or opportunities early

• Help users to spot discernible patterns in their data
• Heatmaps can clearly show favourable and unfavourable variances with colour coding

6.  Enable better team collaboration

• In Power BI Service (powerbi.com) you can closely control who has access to the dashboards
• Power BI KPI scorecards can interact easily with Teams, Powerpoint and Outlook
• You can set KPI alerts and issue important notifications to the team using Power Automate
  

7.  Track financial and non-financial KPI’s in Power BI

• Quite often a key KPI or business driver is non financial
• Power BI can cope equally as well with non-financial KPI’s as financial KPI’s

Some Best Practice tips for a Power BI KPI dashboard:

1.  KPI's must be relevant

• aligned with the objectives of the organisation
• ensure you do not include too many KPI's on one dashboard
  

2.  Choose the appropriate visualisation for your KPI's

3.  Focus attention

• clear layout
• consistent application if formatting of borders, titles, fonts and colours
• use corporate logo colours where appropriate
• use dynamic page and report titles to aid navigation through the dashboard model

4.  Tell the story of the data

• step back and review your final dashboard
• Is your dashboard telling the story clearly and concisely
• Run the 20 second test - can you understand the key points in your dashboard in 20 seconds?

Now that we have explained the advantages of using Power BI for your KPI dashboards, it is now time to dive into Power BI itself! 

SECTION 1 - CREATING FILTERS AND MEASURES

In the above dashboard, we have added three filters on CITY, YEAR and DATES (Current Month or YTD)  and would strongly recommend that you follow along building your dashboards as you follow the step-by-step guide.

If you require the data used to build this dashboard and guidance on the building the dates table, please signup to our blog page below to receive this information.

Once you have followed the guidance, then you are ready to proceed with creating your filters, and subsequently creating KPI Cards by following the instructions laid out below.  

Creating the City Filter

1. Choose a visual 

• Add  a slicer to a blank canvas in the Report pane

2. In the Build visual section, add in a field - CITY

In the Format visual section, click on Visual | Slicer settings | Options | Style - Tile

Update your Selection section  as follows: 

Update  your Values section  as follows or with your preferred style: 

Update  your Border section  as follows: 

Update your Color section as follows: 

3. In the General section, update the following sections: 

Update  your Property section  as follows: 

Update  your Position section  as follows: 

Update  your Title section  as follows: 

Update  your Effects | Background section  as follows: 

Update  your Visual Border  section  as follows: 

Creating the Year Filter

1. Copy and paste the City filter into  blank space in the same canvas 

2. In the Build visual section, under Field, drag in Year 

• Field - Dates [Year]

Note – Use the Dates table and not the Year table

3. In the Format visual section, apply the formatting changes:

Update selection setting by turning Multi-select on. 

4. In the General section, apply the formatting changes:

Update the Property section  as follows: 

Update the  Title section  as follows: 

Creating the Metric Selected Filter

This is a really cool trick that enables the switch between current month and YTD figures.  To set this up follow the steps below: 

1. Click on the Data icon and Select Home and on the Home ribbon, click on the Enter Data icon and the below Create Table dialogue box appears. Then populate the table with the information included and select Load. 

 2. Then, you need to add a measure that determines which options the user has selected. 

Select the Data icon again , and on the far right you will see a Data section, select Revenue data and create a new measure within the data as follows: 

Click on New measure icon and clear out the text Measure = 

Then type in Current YTD Metric Selected = SELECTEDVALUE, as you begin to type in Selected value, this shows up in a down and it's preferable to select this from the drop down as shown below 

Once you click SELECTEDVALUE from the dropdown, the 'Metric Current YTD'[Metric Selected] appears and you need to select this. 

The full measure formula tab should look as shown below and the click the tick   icon.

• Select the Report Tab, Fields section, Right click on the Table Revenues and Select New Measure 

• In the formula bar, type in measures as follows: 

Note: The measures/ formulas should be entered and selected from the drop down as described in section 2 above. 

• Click the tick box to the left of the measure to return or click ENTER on your keyboard. 
• Format: Decimal number, Select: comma [,], Decimal places: 0 

Note 1: SWITCH function: Evaluates an expression against a list of values and returns one of multiple possible result expressions. By using TRUE as a first argument, SWITCH can replace a list of cascading IF statements. It is very similar to the Excel SWITCH function of the same name.

Note 2: The switch statement above will return an error message until you have set up the Amount Revenues Closing Balance and Amount Revenues TYD measures set out below.

4. Create a measure for Amount Revenue Closing Balance 

Notes on Amount Revenues Closing Balance:

5. Create a measure for Amount Revenues YTD 

Amount Revenues YTD = TOTALYTD([Total Amount Revenues], 'Dates'[Date]) 

Note: TOTALYTD function: Evaluates the year-to-date value of the expression in the current context 

6. Create a similar measure Metric Amount Tgt Revenues was built for the Targets using the following measures: 

Metric Amount Tgt Revenues = SWITCH (TRUE(),[Current YTD Metric Selected] = "Current month", [Amount Target Revenues Closing Balance], [Current YTD Metric Selected] = "YTD", [Amount Target Revenues YTD] )

•  In the Format visual section, apply the following formatting changes:
  • From the Visual section: Slicer settings | Selection | Single select - On
  • From the General section:
    • Properties | Size | Height – 133
    • Properties | Size | Width – 235
    • Properties | Position | Horizontal – 707
    • Properties | Position | Vertical – 0
    • Title | Text – Current YTD

•  a base measure that evaluates to a value,
• a target measure or value, and
• a threshold or goal. A KPI dataset needs to contain goal values for a KPI. 

Start by adding a bar chart visual onto the canvas 

1. With KPI cards, it is good practice to add in a bar chart visual in the first instance. This enables you to sort the data as appropriate and then convert to a KPI card visual. The rationale for this is that the data cannot be sorted in a KPI card. Once done we will convert over to a KPI card visual. 

2. Copy and paste the City filter. Tip: it is easier to copy an existing different visual and copy over – as you do not have to reset all of the formatting options.

3. Change the visual type to Stacked column bar chart
 
4. In the Build visual section, add in the fields as follows: 

• X-axis - Dates [Year Month]
• Y-axis - Revenues [Metric Amount Revenues]

5. Make sure the visual is sorted correctly 

• Select the three dots […] above the upper right corner of the visual
• Select Sort axis
• Select Year Month
• Select Sort ascending

1. Change the visual to a KPI card Highlight the visual and select the KPI card visual 

2. You should have the following values in the fields: 

• Values - Revenues [Metric Amount Revenues]
• Trend axis - Dates [Year Month] 

3. Add in the target field: 

• Target - Revenues [Metric Amount Tgt Revenues]

4. In the Format visual section, apply the formatting set out below. 

• Date – On
• Date | Font – Sergoe UI Semibold 9
• Date | Font color – Theta green

5. Create a filter for share class A in the filter pane

• Drag in field Revenues [Share Class] into the Add data field here box Basic filter – Share Class A

6. Add a dynamic title for the KPI card

• You can type in manual titles for each share class if you want - Title | Text – Revs - Share Class A 
  
•  Or else you can use a measure that automatically applies the selected filtered share class to the title. To do this we use conditional formatting for titles 
  
• Set up a new metric within the Revenues table, combining text and vales, as follows: Selected Share Class Revenues = "Revs - " & SELECTEDVALUE('Revenues'[Share Class])
• From the General section: Title | Text ….. Select the Conditional formatting fx button
• In the drop down box select as follows: Format style - Field value | What field should we base this on? – Selected Share Class Revenues| Click OK
• Change the colouring: 
  Title | Text color – Theta green
  Title | Background color – Light Theta green #e0eeee 

• Adjust the background : 
  Effects | Background | Background color – Light Theta green #e0eeee 
  Effects | Background | Transparency – 0%
  
  

• Metric Amount Profit Margin = SWITCH (TRUE(), [Current YTD Metric Selected] = "Current month", [Amount Profit Margin Closing Balance], [Current YTD Metric Selected] = "YTD", [Amount Profit Margin YTD]) 

Note on Amount Profit Margin Closing Balance 

• Amount Profit Margin Closing Balance = CLOSINGBALANCEMONTH( DIVIDE( SUM('Revenues'[Amount Profit]), SUM('Revenues'[Amount Revenues])  ), 'Dates'[Date] ) 

Note on Amount Profit Margin YTD 

• Amount Profit Margin YTD = DIVIDE([Amount Profit YTD], [Amount Revenues YTD]) 

Profit Margin KPI card - amend the formatting 

1. From the Visual section: 

• Callout value
• Callout value | Display units – Auto
• Callout value | Value decimal places – Auto 

2. Add a dynamic title for the KPI card 

• Title | Text …..
• Select the conditional formatting (fx) button

In the drop down box select as follows: 

• What field should we base this on? – Selected Share Class Profit %
• Selected Share Class Profit % = "Profit % - " & SELECTEDVALUE('Revenues'[Share Class]) 

Add in the Sparklines 

1. Select the drop-down box by the Variance field 

2. Select Add a sparkline 

3. From the Sparkline box, select the following options: 

• Y-axis - Variance
• X-axis – Year Month

4. In the Format visual section, apply the formatting: 

From the Visual section: 

• Sparklines
• Sparklines | Apply setting to | Sparkline – Variance by Year Month
• Sparklines | Sparkline | Chart type – Column
• Sparklines | Sparkline | Data color – Orange #F17925

5. Adjust the columns in the visual

Well done for making it to the end, you have now learnt how to create filters, add new measures and ultimately how to build KPI Cards. In the next series, we will walk you through how to build heatmaps. If you have found the contents useful, please leave us a comment below.

GOOD LUCK!

THE INSIGHTS+MODELLING TEAM

DISCLAIMER

The publication has been prepared for general guidance on matters of interest only, and does not constitute professional advice. You should not act upon the information contained in this publication without obtaining specific professional advice. No republication or warranty (express or implied) is given as to the accuracy or completeness of the information contained in this publication, and, to the extent permitted by law. Theta Global Advisors LLP, employees and agents do not accept or assume any liability, responsibility or duty of care for any consequences of you or anyone else acting, or refraining to act, in reliance on the information contained in this publication or for any decision based upon it. Ó 2023 Theta. All rights reserved. Not for further distribution without the permission of Theta.