When to Use Heatmaps and How to Implement Row-Level Heatmaps in Power BI

[Ritaf1983]

Scenario:

Let's assume I have a factory with a large number of machines. These machines produce different products. The factory operates 24 hours a day. I'm interested in seeing what the peak hours are for machine malfunctions and which machines are the most problematic. The goal is to understand where more in-depth investigation is needed to improve the situation. To answer this question, I need to examine trends in the number of malfunctions by hour. If I choose a line graph, with the number of machines I have, it will look like this:

Challenging!!!

Let's think of another alternative.

The alternative

The graph that can help convey the message without creating cognitive overload in this case is a Heatmap.

 Its tabular form allows for the presentation of extensive data in a single view without overloading.

Data comparisons in a heatmap are indicated by the intensity of the color:

the stronger the intensity, the greater the value being represented.

However, it is important to note that while a heatmap allows for a rough estimate of data trends and helps locate peaks for further investigation, it may not accurately depict exact relationships between data points. For instance, you might not be able to precisely see that there were three times more errors at one hour compared to another. Nonetheless, a heatmap offers a quick and simple way to identify areas that require more detailed analysis.

 The challenge 

The default heatmap in POWER BI compares the metric at the level of all values in the table. This means that it does not take into account the fact that these are different machines and that the required comparison is at the machine (row) level. In my data example, machines r and j have a very high number of errors. Because of this, machines a, y, and others that have few errors in general, their peaks are less prominent, if at all

The solution

Work with DAX to create a measure that allows to change the default settings.

How to do?

1. Create a basic measure to calculate the number of the breakdowns :

Total breakdowns = sum('Table'[Brakdowns])

2.  Create a measure that ranks the hours by the number of breakdowns at the machine level, which will allow us to apply the formatting at the row level:

Heat map ranking = rankx(filter ( all('Table'[Hour]),[Total breakdowns]<>0),[Total breakdowns],,asc)

3.  Drag the machines to the rows, the hours to the columns, and the number of errors to the matrix values.

4. Apply conditional formatting based on the "Heat map ranking" measure 

Result :

Additional tips:

1.  The heatmap must include a legend. Since this cannot be done directly in Power BI, you can use PowerPoint or another graphics software and import it as an image.

2.  Despite the trendy use of heatmaps with multiple colors, the most effective and easy-to-read approach is to use a single color with varying intensity. Our brains can quickly grasp the relationship between color intensity and data magnitude. Introducing multiple colors adds a decoding element that requires more cognitive effort.

The use of multiple colors is only justified when there are more than one data series. Even in such cases, careful consideration and testing are required to ensure accessibility for colorblind individuals

That's all, thanks for reading, and good luck with the implementation 🙂

PBIX is attached

Heat map (by rows).pbix