How to use Conditional Formatting based on a Measure in Power BI

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.

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.

Adding 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. And 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.

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.

Rick de Groot
About Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

Leave a comment