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