Welcome to the second blog of my two-part blog series on Enhancing KPI indicators and visuals in Power BI. As a recap, Part 1 focused on displaying a green ‘up’ or a red ‘down’ arrow indicator for our KPI card visualization based on a pre-defined threshold.
Another common request by business users is the ability to see at-a-glance in a visual how the categories are performing based on another measure. For example, in some business scenarios, the user would also want an intuitive high-level view of which products have underperformed in sales in comparison to a target by highlighting them in red. Until recently, there was no way to achieve this out of the box for visuals. However, thanks to the impressive new feature “Color saturation on visuals upgraded to use conditional formatting” in the recent November 2018 release (Power BI Desktop), it is possible to implement this functionality without much effort. Read on to find out.
I start with a simple Sales table with Date, Product and Revenue columns.
The first step is to create three simple DAX measures to calculate the total sales revenue, revenue for the previous year and the year over year change.
Next, a bar chart visual was added to show the total revenue by country.
The percentage year over year growth in sales could broadly take on three possible values as shown below:
The goal is to highlight all products that have had a negative Year Over Year percentage growth in sales. To achieve this, I create my final measure that uses a simple SWITCH function to return 1 for positive YoY % values, -1 for negative values and 0 when there is no growth in sales.
Now comes the fun part. Power BI has upgraded its color saturation feature by removing it from the field well and instead including an additional conditional formatting feature for Data Colors under the formatting pane. This upgrade is available for all visual types. As a result, the user now has the option to apply advanced color formatting for visuals based on different rules/fields. With the visual selected, clicking on the Advanced Controls link under Data Colors in the formatting pane will launch the dialog for conditional formatting. From here, it is simply a matter of applying the conditional formatting to our YoY Flag measure. the Format by option to ‘Rules’ from the drop down and the field option to the YoY Flag measure. It can be recalled from our YoY Flag measure that the flag -1 signifies negative growth. Hence, we use that as the condition for our rule. The image below shows the color rule that I have applied:
As I am only interested in highlighting the negative growth, a single color rule is all that is required. The user may apply additional rules should they wish to highlight the positive growth and no-growth products in colors different from the default.
Click OK and the color changes will reflect in the visual. As the color rule is defined on a DAX field, the bar colors in the visual would change dynamically to produce the desired results for different slicer selections.
That brings us to the end of this blog series on enhancing KPI indicators and visuals. Hope you found it enlightening and useful. Thanks for reading.
Hasta la vista! 
Check out part 01 here: https://bit.ly/2WfrfIw
Author:
Jeevashini Selvadurai
Associate Consultant – BI
Jeevashini is an Associate BI Consultant at Fortude with a deep fascination for Applied Mathematics and Data Analytics alike. She has over 2 years experience in the finance and BI domains. Her skill set ranges from financial fraud analytics to data modeling and developing dashboards with Microsoft BI technologies.
