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.

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

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

• 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

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

Learn more about Grouping and Binning here.

Now it’s time to add a measure to calculate the average for each cell as explained in the beginning of this post, using the Excel sheet as an example.

Smoothing Avg =
VAR __weightBinSize = 5   --bin size of our columns
VAR __totalWeightBin = __weightBinSize * 2
VAR __myWeightBin = SELECTEDVALUE( FlatData[Weight (bins)] )  --get current weight bin
//establish range for weight bin. Go back two columns (bins) and go forward two columns (bins)
VAR __minWeightBin = __myWeightBin - __totalWeightBin   --go back 2 columns
VAR __maxWeightBin = __myWeightBin + __totalWeightBin   --go forward 2 columns
VAR __lengthBinSize = 150   --bin size of length columns
VAR __totalLengthBin = __lengthBinSize * 2
VAR __myconcSpan = SELECTEDVALUE( FlatData[Length (bins)] )  --get current length bin
//establish range for length bin, go back two rows and go forward two rows
VAR __minLengthBin = __myconcSpan - __totalLengthBin    --go back 2 rows
VAR __maxLengthBin = __myconcSpan + __totalLengthBin    --go forward 2 rows
//sum the quantity for the range, it is similar to A1..E5 in our excel example above
VAR __totalQty =
    CALCULATE(
        SUM ( FlatData[Qty] ),
        FlatData[Weight (bins)] >= __minWeightBin ,
        FlatData[Weight (bins)] <= __maxWeightBin ,
        FlatData[Length (bins)] >= __minLengthBin,
        FlatData[Length (bins)] <= __maxLengthBin
    )
RETURN
    DIVIDE( __totalQty, __totalWeightBin )  --calculate the average

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:

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

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:

Looks pretty good, but let’s check out how it looks after we use the data smoothing average 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:

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:

Read more about conditional formatting here.

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!

Useful Links

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