With conditional formatting in Power BI, you can apply formatting to your values based on conditions. For example, you can format a cell’s background based on the value in a cell. The user interface offers several formatting options. Yet, when working with conditional formatting, you may soon bump into the limitations of the user interface. Basing your formatting on a field value could then be a solution. In this post, you will learn how to apply conditional formatting based on a measure, which virtually allows for limitless formatting options.
Table of contents
Introduction
Recently, a client asked me to create a heatmap in Power BI. The Heatmap shows the number of appointments in a matrix, split by month (in the columns) and day (in the rows). Without any visual cues, the heatmap can be quite overwhelming. There simply are a lot of numbers shown in a single visual. To make the visual easier on the eyes, you can apply a conditional format to the background of each cell.
Basic Conditional Formatting
In a table, you can add conditional formatting by clicking on the arrow next to the measure in the Values section. Then click Conditional Formatting -> Background Color. This will open the settings menu where one can configure the formatting rules. In this case, we will apply the following settings:
Apply to: Values only
Choose: minimum (lowest value), maximum (highest value)
Apply white colour to the lowest value, and dark green colour to the highest.
And there you go! These are the first steps to creating a heatmap. And for some datasets, this may work, especially when your data is distributed evenly over time. The additional challenge to this heatmap, however, is that it has a strong seasonality pattern. Most sales are in November and December. And when you then base the background colour on the minimum and maximum of the entire table, the brightest colours will be in January and December. In this case, the heatmap would be more informative with colours based on the distribution per month. So how can you do that?
Conditional Formatting Using a Measure
Seasonality impacts the distribution of the data, and the client wants to conditionally format the background based only on the numbers within the same month. To achieve that, you can write another measure that calculates the amount of each day stated as a percentage of the total month.
Appointments % of Month =
VAR Appointments = [# Appointments]
VAR AppointmentsMonthlyTotal =
CALCULATE ( [# Appointments], ALL ( Calendar[Day Number] ) )
VAR Result =
DIVIDE ( Appointments, AppointmentsMonthlyTotal )
RETURN
Result
Resulting in the following table:
Notice that each column that focuses on a month amounts to 100%, regardless of the size of the numbers. With this new measure, you are now equipped to apply the conditional formatting to the background of the first table. To do that, in the first table, go to the conditional formatting settings. In the Based on field section, select the newly created measure Appointments % of Month.
Apply the changes and notice how the new formatting is applied to the heatmap.
Conclusion
As you can see, conditional formatting based on a measure opens up a wide range of possibilities – such as redistributing your dataset. Further application in this area is only limited to your imagination.
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Wһat’s up mates, how is all, ɑnd what you want to say regarding this pߋst, in my view its
genuinelʏ amazing in favor of me.
This almost works for my needs
However not quite.
I am trying to do this very thing over a matrix of Market Shares
Columns of Dates
Rows of Countries
This issue as I see it, is that because my Market Shares are already in Percentages doing a Percentage of percentages, although you get a result the conditional formatting doesn’t quite hit the mark
Hi Martin, have you considered taking the absolute difference between percentages for your formatting? Perhaps you like it better for formatting the percentages.
Sorry it works all fine, just me who had miss understood the meaning.. Instead of using percentage I have used RANKX to rank all hours within a given day. By doing so the max value within a day will have the same across days and thereby color format the way I intended it to format.
Thanks again for a great video!
Great video and article! You have solved exactly the problem I am struggling with.
However, how does your data model and # Appointments measure look like? I have manage to recreate everything until 4.18 min with my own data. However when I move the conditional measure to the conditional formatting statement, it will not change the rendering of the background color as it suppose to do?
Do you have an idea why this is happening?
Hope you can help. Thanks for your time!