How To: Create the missing KPI Indicator In Power BI Desktop Using Conditional Formatting (Updated)

BySteve Young Updated on

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.

**Update Note:  This example walks through a more complex variance to budget example and allows you to create a table of icons to use in traffic lighting.  You can create a column in a table by adding a measure using the DAX UNICHAR function.  For example, UnicarIcon = unichar(9733) will provide a Star as pictured below in the far right column.   You can find more Unicode characters on the Unicode Table site.  Or, use this site, which will give you arrow codes when you highlight the icon you wish to use.

You would then format the Unichar Icon column the same way as the tutorial below. Select the Unichar Icon measure, use the triangle drop down and select Conditional Formatting.

Then select the Formatting based on the score column, which we will use the Fcst Bud Pct column.

That provides us with the following final version with the stars, unichar(9733), with the score value colors as shown below in the far right column.

SO, that being said, if you use this measure,
MultiUnichar = IF([FCST Sales Score] = 1, UNICHAR(129093),IF([FCST Sales Score]=2,UNICHAR(129094),UNICHAR(129095))) you can have different icons depending on what you want to display.
This uses a score to determine what color and what icon to use, providing the type of solution pictured below.


There are many options available and the tutorial below goes through a full example of creating scores and using a table to drive the icons. The above note makes this easier, if you do not want a data driven icon.

Tutorial Goal

  1. Learn how to use Conditional Formatting in a Matrix Table
  2. Use the conditional formatting and a table of icons to function as a KPI indicator

Scenario

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.

Interactive Sample

Full-screen mode is available by clicking the icon on the bottom right of the panel.

Let me know if you find this tutorial and solution helpful.   Feel free to DM me on Twitter, or follow me at Steve Young (@5MinuteBI) | Twitter.

Steve Young

Reference

You may find the following resources helpful for trying these features out.

With over 34 years of experience in the data and technology industry, the last 16 with Microsoft, I have had the opportunity to work in various capacities, contributing to my knowledge and expertise in Data Engineering, Power BI, and Data Visualization.