The course is part of this learning path
Power BI scorecards are designed to display KPI-type metrics where actual or performance measurements are compared to target or predicted outcomes. In this course, we look at setting up scorecard metrics using various data sources, along with formatting and display options.
- Learn how to create scorecard metrics
- Learn how to configure metrics data sources
- Examine different use case scenarios
- Students wanting to learn about Power BI scorecards
- Those preparing for the PL-300: Power BI Data Analyst exam
- Familiarity with the Power BI service interface
After logging into app.powerbi.com, click the metrics trophy button on the main menu and then the new scorecard button at the top right.
Let’s start by adding a manual metric comprising a single actual and target value to our untitled scorecard.
I call the metric manual sales KPI. I’ll choose manual metric from the current value settings and enter a current value. I’ll do the same for the target value. The actual value is called current, and the target is called final to emphasize that this is a point-in-time metric on the way to a final destination.
We can set up a status to be displayed as a label that will allow users to filter scorecards based on their status. A scorecard’s status can be determined by a series of rules made up of multiple conditions. A rule can compare the actual or current value with some absolute value. I can repeat the target value here, but comparing it with a percentage of the target value would make more sense. By the way, rules can also compare the date or a change in value with the target. When the conditions are met, and we’re on target, we change the status to on track. I need another rule for when we’re below target. Actually, what I’ll do is create a rule for when we’re 75% below target with a status of behind and a rule for when the value is less than 100% of target with a status of at risk. The rules are evaluated from top to bottom, so the order is important. You can use the arrows at the bottom right of a rule to move it up or down. Once you’ve set up your rules, click save.
You can set up a metric to be an aggregate of sub-metrics. I’ll set up northern and southern region sub-metrics with manually entered data.
As you can see, I don’t need to select manual from the drop-down list, I can just start typing into the current value and final target fields.
I won’t bother with status rules for these sub-metrics. Next, the southern region. With the sub-metrics in place, I can go back to Manual Sales KPI and change the current value and final target data sources to use sub-metrics with a sum aggregation.
This is all a bit one-dimensional, but we can manually enter time-series data for actual and target values with edit multiple values. I’ll enter a few values for the northern region. First, the actual values with a corresponding date and then the target values. At the bottom left, you can choose to display the final target or the next milestone. With multiple values entered, the manual sales KPI scorecard updates the totals and the status, and we get a trend line. Clicking on the scorecard, not the trendline, enables us to drill down into the metric’s details. The details pane shows a check-in history of the manually entered data. When you share the scorecards, anyone with the appropriate permissions can add or modify data, so the check-in history audit log is an important feature. Also, this is another way to get to the rule’s editor. By default, metrics are tracked on a daily basis, but you can override this behavior within time period. When I set up quarterly cycle to date, we can see how the monthly detail has gone from the trendline graphic, and the label has been replaced with the one I entered.
Manually entering data is most likely going to be the exception to the rule, so let’s create metrics connected to a dataset. I’ll create a metric called real sales KPI, and I’ll select connect to data for the current value setup. We’re not going to get the data from an actual dataset but from a report. Select the report and click next.
With the sales graph selected, I’ll click on the sales data line and leave the time axis on “track all data in this time series.” I’ll connect to the same graph for the final target and link to the sales target line. Next, I’ll set up a couple of rules for setting the status when we’re on track or below target. Finally, I’ll set up a margin KPI, connecting to the margin graph of the scorecard data report. Again, I’ll create the same rules for displaying the margin KPI status.
I know some of you will be thinking the status label text isn’t quite right. You can customize the label text through settings. I’ll change on track to on target and overdue to below target. You can also change color, although the pallet is a little muted. Last but not least, let’s give the scorecard a name.
Across the top of the scorecard, we have a count of the metrics with each status. Clicking on the status will filter the scorecard, showing the relevant metrics. You can share the scorecard via email and teams or copy the link, remembering that only those with appropriate access can view it.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.