Heat map technique with data smoothing

More than a year ago, a question was asked on the Power BI community in regards to showing a heat map in Power BI. Not just a simple heat map in the matrix visual, but a heat map based on data smoothing. I cannot find the link to that post (sorry for that!), but I want to share what I learned from it and how I solved it.

The business logic was to smooth data by taking the average of the surrounding two rows and two columns in both directions and to take the average of the value based on the bin size on the columns. In our case, we are assuming that the bin size value for the column is set to 5.

To explain this further, assume we have the following data in Excel and we want to calculate the average of Cell C3; we need to look at the adjacent two rows and two columns.

Smoothing Avg Data Example

In this case, the total values will be 243 (SUM A1:E5). For C3, which is in row 3, we are adding the values from row 1 to row 5, and similarly for column C we are adding the values from column A to column E; that’s what translates to A1 to E5 (SUM A1:E5). The average value for this cell (C3) will be 24 – 243 divided by 10. You get 10 by multiplying the bin size (5) by 2, and then 243 divided by 10 rounds to 24.

I hope this gives you an idea on how we are calculating the average of each cell to smooth our data. Let’s see how it all fits into Power BI and how the heat map looks after data smoothing.

Here is some sample data with three columns:

Length

Weight

Qty

Raw Data

In this example, Length will be on rows with bin size of 150 and Weight will be on columns with bin size of 5.

Let’s first add a bins column to our table in order to add the bin for the Length column.

Right click on the Length column

Select New Group

How to add bin?

In the New Groups window, add the bin name – in our case it is called Length (bins) and give it a Bin Size, i.e. 150

Bin Settings

Similarly, we are going to add Weight (bin) with the Bin Size value set to 5. Once the bins are added, we will have two new bin columns in our model: Weight (bin) and Length (bin).

Alright, now that our measure is in place we can create a matrix with a heat map. To do so, select the Matrix Visual and add Length (bins) on rows, Weight (bins) on columns, and our average measure (Smoothing Avg) in the Value section. This is what it will look like:

Matrix

Once all of the above is done, this is what the matrix will look like. I love numbers, but this looks super boring:

Raw Data

So, now it’s time to create a heat map, and for this we will use conditional formatting. Before that, however, let’s see what a heat map without data smoothing would look like:

Raw Data Heat Map

Looks pretty good, but let’s check out how it looks after we use the data smoothing average measure.

Heat Map with Smoothing Avg Measure

Looks better – but let’s take it even further. We really don’t need to see those numbers; in the conditional formatting window, let’s change the foreground and background to the same colour. Here is the output:

Heat Map with Smoothing Avg no number

I find the above heat map to be much more useful than a heat map without data smoothing.

And this is what we are doing in the conditional formatting window for both the foreground and background colour:

We can do a lot of amazing things in Power BI to visualize our data and this is just one example. How will you use it? As always, I’m looking forward to your feedback and learning from you all!

About the Author

Parv Chana is a BI Architect and Consultant with over 20 years of industry experience. He runs a small consulting company, PeryTUS IT Solutions, where he implements Power BI solutions for his clients, which range from small to large enterprises. He has experience working within a wide variety of industries and has deployed many unique solutions to solve his clients’ BI needs. He is a Microsoft Data Platform MVP and has been working with Power BI ever since it was made available in 2015.

His areas of focus include implementing end to end Power BI solutions (from data modelling to visualization), analyzing existing Power BI solutions, helping organizations follow best practices for scalable BI models, and providing training to help his clients achieve their BI goals.

Useful Links

Learn more about Grouping and Binning here. Read more about conditional formatting here.