How To: Create the missing KPI Indicator In Power BI Desktop Using Conditional Formatting
Power BI lacks the ability to add KPI calculations when creating a data model in Power BI Desktop. I will show you how to get the same functionality by using the recently released feature “Conditional Formatting by a Different Field”. This will allow you to take a calculated score and have an Icon displayed for each row in your table. A working Live Interactive Power BI Sample is attached at the bottom of this post.
- Learn how to use Conditional Formatting in a Matrix Table
- Use the conditional formatting and a table of icons to function as a KPI indicator
The goal of the final visualization is to show the performance of This Year Sales against Last Year, Budget, and Forecast (FCST). The key is to get a KPI Score based on the percentage of the goal to the current month’s sales. This example will cover a couple of steps and will use the Current Forecast Sales vs the Budget Sales. The Power BI workbook is available in GITHUB.
This is the final model with the calculations added.
Step 1 – The Example Table
As you see below, the Current Year Sales for the Brantford Facility is shown with comparative values, Last Year Sales, Budget Sales and Forecasted Sales. We want to show how these metrics compare to the Current Sales Forecast.
Example Note: A forecast is entered each month for the year. As the year goes on, once a month passes, the forecast becomes the Actual for that month so that the total year sales can be forecasted.
The Forecast Sales is shown in detail in the example, however, each calculation uses the same pattern.
Step 2 – Calculations
The first step is to calculate the percentage to target using the following DAX formula. This could simply be a variance percentage, but visually if the value is 100, then you are at the forecast rather than showing a variance of zero and also having negatives in the column if you are under target.
Fcst Bud Target Pct = (DIVIDE([FcstSales],[BudSales],0))
I used Forecast to Budget for the remaining year as the Budget Sales was the metric used to measure the remaining year forecast. This value is shown in the table above. The management’s goal is a 10% growth from the budget.
The following DAX formula provides the score.
FCST Sales Score = if(OR(ISBLANK([FcstSales]),ISBLANK([BudSales])),””,IF([Fcst Bud Target Pct] > 1.10, 1, IF([Fcst Bud Target Pct] < .9, 3, 2)))
If either part of the calculation is missing, we want to show a blank value which is taken care of in the IF/OR part of the function.
- If the value to target is > that 110% we want a 1 which will translate to a Green value.
- If the value to target is < 90% we want a 3 will translate to a RED value.
- The values in between will show an in-between will be a Yellow value.
The table below has the calculations for the Forecast vs Budget Sales and the score.
Step 3 – Creating the Highlighting
To illustrate the highlighting functionality, we first want to have the background colors of the cells to match the FCST Sales Score we have calculated. The highlighting is found under each Value you have in the table. Selecting the downward triangle brings up the first flyout window. Select Conditional Formatting, then Background Color Scales.
The picture below shows how I set up the Forecasted Sales. The Value you selected is displayed in the Apply Color To box, then select the Value you wish to use to determine the highlight by in the Color Based On drop-down.
Rather than using a continuous gradient or diverging color, select Color by Rules to “Fix” the color to the score. You can add more than three. The Rules are entered depending on how you want the highlighting to work. This example provides more of a traffic light style KPI. The calculation provides a 1,2 or 3 value, and this is used to display a Red, Green or Yellow background color.
Step 4 – Adding the KPI Icon to Each Row
Now the fun part !!!! Using an interesting way to add in a KPI indicator to each row of the table. First, I added a new table using, Enter Data on the toolbar. The final version is shown below. If you go to a location such as Wikipedia – Geometric Shapes, you can copy the text value of one of the shapes, and paste it into the column of the table. Providing an Icon Name will allow you to filter the visual you use the icon in to show different icons.
Once you have the table, drag the ICON measure to your table.
Because the Icon table is not attached to the data model, you have to use the “First” summary option for the Value.
Using the same steps as above, put conditional formatting on the First ICON measure.
Once your formatting is in you should see the icons correspond to your colors and score as shown below.
Note: When you do select your text elements for the ICON, some text may have a color and will not take the Conditional Formatting. They need to be basic Characters. See the BigCircle and the Checkmark in the live example. Test what you select and also test in various browsers.
To change the ICON, add the Icon Name to the visual filter section when the table selected. You could also use the Page or Report filters, however using the Visual level allows you to have different graphics on different visuals on the page. The below version allows you to select what you want to see.
Step 5 – Final Version
This is the final version which is also available as a live public report at the end of this post.
Notice that Totals Are Not Formatted
Conditional highlighting does not continue to the totals. This is something that is missing and there is an idea, which is a suggested feature in the Power BI Community that people can vote on. The more votes, the more the product team can see that this is in demand. Help out by going to the site and vote it up, Conditional Formatting for Total and Subtotals in Matrices.
Full-screen mode is available by clicking the icon on the bottom right of the panel.
You may find the following resources helpful for trying these features out.