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.
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:
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:
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
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:
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
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.