Skip to content
Home

KPI Dashboards in Power BI part 3: Building a dashboard - KPI scorecards

Published by Simon Jeffery on

 

Introduction

 

KPI Scorecards are a centralized platform for visualizing and analyzing KPIs:

  • Enables organizations to view overall performance in an user friendly way
  • Great for communicating clearly with the team
KPI scorecards become even more powerful when you combine them with Power Automate to create alerts to goal owners if the status of a goal adversely changes
 
Microsoft now refers to them as Metrics but they used to be known as goals so you might see references to either
 
KPI scorecards are created preferably in Power BI service (powerbi.com)
 
In this section we will:-
  • Create some simple line graph visuals in our Power BI desktop model  – to be used as a basis for the KPI scorecards
  • Load the model up into Power BI service (powerbi.com)
  • Create a simple KPI scorecard
  • Create a simple performance management workflow in Power Automate

 

SECTION 1 - BUILDING A KPI SCORECARD – CREATE LINE GRAPH VISUALS

 

In this section we will create some simple line graph visuals – to be used as a basis for the KPI scorecards

1. In your Power BI desktop model (which you have been using for parts 1 and 2 of this blog), open a new tab New revenues time series
 
2. Copy the visual Revenues – Share Class A onto this canvas
 
  • Right-click on the Revenues – Share Class A
  • Select Copy and then Copy visual

  • Paste in the new tab New revenues time series
3. Set up the new visual
  • Change the visual type to a line graph

4. In the Build visual section, change the field
  • X-axis - Dates [Days Current Year]
  • Y-axis - Dates [Amount Revenues YTD]
  • Y-axis - Dates [Amount Target Revenues YTD]
Note – Use the Dates table and not the Year table

5. Note on Days Current Year

The coding in the Dates table to create this field is as follows:
  • VAR PreviousDates = if([date] <= today(), [date])  // Any date before today’s date
  • VAR YearDate = YEAR ( TableDate )
  • VAR ThisYearDates = if(YearDate = year(today()), PreviousDates)  // Any date in current year
  • "Days Current Year", ThisYearDates,
6. In the Format visual section, apply the formatting changes:
  • From the Visual section:
    • Slicer settings | Selection | Single select - On
  •  From the General section:
    • Properties | Size | Height – 230
    • Properties | Size | Width – 554
    • Properties | Position | Horizontal – 0
    • Properties | Position | Vertical – 0
    • 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 YTD
    • Click OK
 Selected Share Class Profit % = "Profit % - " & SELECTEDVALUE('Revenues'[Share Class])
 
  • Adjust the title formatting
    • Title | Font color – White
    • Title | Background color – Theta green
Other Line graph visuals
 
  • Copy the Line graph for Revenues – Share Class A
  • Update the filter pane for Share Class B
  • Copy the Line graph for Revenues – Share Class A
  • Update the filter pane for Share Class C
  • Copy the Line graph for Revenues – Share Class A
  • Update the filter pane for Share Class D
  • Copy the Line graph for Revenues – Share Class A
  • Delete the filter pane for Share Class A
  • Add a new filter pane for Overall Revenues
    • Filter Type – Advanced filtering
    • Show items when the value – is not blank
    • Click on Apply filter

  • Update the report title for the Overall Revenues visual
    • From the General section of the Format visual section:
    • Title | Text – Overall revenues (£ k) – YTD

 

SECTION 2  - BUILDING A KPI SCORECARD – LOAD THE MODEL UP INTO POWER BI SERVICE (POWERBI.COM)
 
For this you will need a Power BI Pro licence. If you do not have one you can get a free trial version that lasts for 60 days
1.  Save the model

2.  Click on the Publish icon to publish the model to the Power BI service (Power BI.com)

  • Home | Publish
  • At this point you might be prompted to log onto your Power BI service – log in as normal


3.  Select the workspace My metrics workspace
  • The report will load in Power BI service
 
4.  If the visual has already been loaded into Power BI service, you will get the message below.
  • If OK select Replace

5.  When loading you will see this message box
 

6.  Once loaded, you will see the message box below.

  • Select Open ‘Theta CIMA presentation February 2023’ – Power BI model.pbix’. in Power BI
The report in Power BI service (PowerBI.com) will open for you

 

SECTION 3 - BUILDING A KPI SCORECARD – CREATE THE FIRST KPI METRIC IN THE SCORECARD
 
1.  Within Power BI service click on the Metrics icon
 
2.  Click on + New scorecard


You will see this screen below which is to be completed:
 
3.  Give the scorecard a name
 
  • Edit the name on the top left of the screen
  • Name the scorecard as Theta CIMA training scorecard
4.  Name the first metric
  • Metric name – Overall revenues
5.  Update the owner(s)
 
  • We will use the default
  • Owners – Simon Jeffery
6.  Update the Current value
 
  • Select the Set up drop-down box
  • Select Connect to data

7.  Select a report or app to update
 
  • Select the first entry in the list:
  • Report name – Theta CIMA presentation February 2023 – Power BI model
  • Workspace – My metrics workspace
  • Click on Next
 
8.  Select the worksheet tab that holds the report that underpins the scorecard:
 
  • New revenues time series
9.  Select the bottom visual: Revenues (£ k) – YTD
 
10.  From the dropdown box Choose a measure to connect
 
  • Measure – Amount Revenues YTD
  • Select Track all data in this time series

 
11.  Click on Connect
 
12.  Go back to the Theta CIMA training scorecard
 
 
13.  Update the Final target value
 
  • Select the Set up drop-down box
  • Select Connect to data
 
14.  Select a report or app to update
 
  • Select the first entry in the list: Workspace – My metrics workspace
  • Click on Next
 
15.  Select the worksheet tab that holds the report that underpins the scorecard:
  • New revenues time series
16.  Select the bottom visual: Revenues (£ k) – YTD
 
17.  From the dropdown box Choose a measure to connect
 
  • Measure – Amount Target Revenues YTD
  • Select Track all data in this time series

 
18.  Click on Connect
 
19.  Go back to the Theta CIMA training scorecard
 
  • Click on the Current value – 6M
  • Change the unit to Decimal number
  • Change the number of decimal places to 1

 
20.  Click on Save to save the work done so far. The KPI metric will look like this
 
 
 
21.  Now we will set the rules for the status icon for the KPI metric.
 
  • It currently shows Not started
  • Click on the Edit button
 
 
22. In the Status box click on or set up rules



23.  Click on + New rule
 
 
24.  In the Overall revenues box type in:
 
  • If Value | is greater or equal to | 100 | % of target
  • Change status to On track
  • Otherwise change status to Behind
  • Click on Save
  • Click on X in top right hand corner of Overall Revenues box
  • Click Save to update the KPI Metric
Note that when I selected Save I received a Teams message to say that the status of the Overall revenues has been updated. More on that later
 
25.  From the KPI Scorecard screen select Read mode
 
 
The first KPI metric has been successfully updated
 
 

 

 

SECTION 4 - BUILDING A KPI SCORECARD – ADD IN A SUBMETRIC TO THE KPI METRIC THAT WE HAVE JUST CREATED
 
We will add in a submetric to the KPI metric that we have just created
 
1.  From the KPI Scorecard screen select Edit mode
2.  Click on the three dots - more options

3.  Select new submetric
4.  Follow the instructions in the section above and add in a new KPI metric as follows
5.  Submetric name – Share Class A revenues
6.  Current value | Set up - Connect to data
7.  Select a report or app to update
  • Report name – Theta CIMA presentation February 2023 – Power BI model
  • Workspace – My metrics workspace
8.  Select the worksheet tab that holds the report that underpins the scorecard:
  • New revenues time series
9.  Select the top left visual: Revenues Share Class A (£ k) – YTD
10.  From the dropdown box Choose a measure to connect
  • Measure – Amount Revenues YTD
  • Select Track all data in this time series
11.  Click on Connect
 
12.  Go back to the Theta CIMA training scorecard
 
13.  Repeat 7-12 for updating the Final target value

14.  From the dropdown box Choose a measure to connect
 
  • Measure – Amount Target Revenues YTD
  • Select Track all data in this time series
15.  Click on the Current value – 6M
 
  • Change the unit to Decimal number
  • Change the number of decimal places to 1
16.  The submetric now looks like this

 
17.  Update the status icon as per the above section
  • Click on or set up rules    
18.  Click on + New rule
19.  In the Overall revenues box type in:
  • If Value | is greater or equal to | 100 | % of target
  • Change status to On track
  • Otherwise change status to Behind
  • Click on Save
  • Click on X in top right hand corner of Overall Revenues box
  • Click Save to update the KPI Metric 
Note that when I selected Save I may have received a Teams message to say that the status of the Overall revenues has been updated. More on that later
 
20. From the KPI Scorecard screen select Read mode
The first KPI sub metric has been successfully updated
 
 
 
SECTION 5 - BUILDING A KPI SCORECARD – ADD IN THE THREE SUBMETRICS TO THE KPI METRIC FOR THE OTHER THREE SHARE CLASSES
 
Repeat the steps above for
  • Shares Class B revenues
  • Shares Class C revenues
  • Shares Class D revenues
 1.  Select Overall revenues
 
 
2.  Select + New | Add submetric
 
3. The completed version looks like this:

Tip: Make sure that this scorecard is saved in the correct workspace

 
Conclusion: 
Now you have learnt how to create line graph visuals in PowerBI as a Service. If you have found this post useful or if you have any questions, please leave us a comment below to help us keep improving our content to you. 
 
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.