Part I – How To Create a Heatmap Data Visualisation Based On a Ticketing System

If you prefer to watch a video tutorial please scroll down to the bottom of the page ⤵️

What Prompted The Idea?

Well, I was walking past a Post Office when I saw the below image

Image of a heatmap at my local post office indicating busy periods

I had to go back and take a picture as I found this Data Visualization pretty interesting. I reckon for most customers* it is super easy to understand and it does reveal a good data story.

*Given that it has Red/Yellow/Green colours to indicate busy-ness it might not be the most accessible report, but still a good start.

Then my second thought was that I should recreate this in Power BI – and of course share a how-to video and a blog post with the community.

No Built-In Option

If we take a look at the default Power BI Visualizations options we cannot find a heatmap or a heatmap style visual…

Default DataViz options in Power BI

As in most cases, it doesn’t mean that there is no way to recreate a heatmap style report element.

It only means that a little bit of tinkering and outside-of-the-box thinking is needed.

I don’t want to ruin the surprise but our ALL TIME FAVOURITE visualisation option comes to the rescue – The Matrix visual!!!

What Do We Need?

Apart from the RAW data – which in our case is going to be a Ticket ID and a Ticket Out field we are going to need a DATE table and a TIME table.

RAW Dummy Data

From the Ticket Out field we can get a date and a time only field using Power Query’s GUI, like this:

Power Query – Add Column – Date / Date Only
Power Query – Add Column – Time / Time Only

Create a Date and a Time table

Why do we need a Date or Calendar table and a Time table separately?

Well, it is considered to be a best practice within Power BI.

Why? The reason behind that is actually logical. If we “enhance” or “fill up” the Calendar table with ALL the time details we would like to use in the report then a massive table (a Frankentable) would be needed.

Imagine having all the hh:mm combinations created for ALL days in a Date table. For a single year (365 days) it would mean an additional 365 x 24 x 60 = 525k records. And this is just for the hour-minutes combinations, imagine how big of a table it would be if we would include seconds as well…

The good thing is that for the heatmap visual we try to recreate in this demo we only need a few details. So both of our “time intelligence” tables could be fairly “narrow”.

For the Date table I only need a list of dates, a day name (Mon, Tue, etc) and a day ID (1-7 for sorting) field. It means that my DAX is relatively simple.

It looks like this:

Calendar =
    “Day”FORMAT ( [Date], “Ddd” ),
    “Day Sort”WEEKDAY ( [Date], 2 )

As for the Time table I also don’t need a lots of fields as my heatmap will only show hh to hh (like 8-9 or 14-15).

So I used the below DAX to generate my Time table:

Timetable =
VAR base =
        GENERATESERIES ( TIME ( 000 )TIME ( 23590 )TIME ( 001 ) ),
        “Time”, [Value]
VAR result =
        “Hour”HOUR ( [Time] ),
        “Hour Bracket”,
            HOUR ( [Time] ) & ” – “
                HOUR ( [Time] ) + 1

Lastly a single DAX measure to count the Ticket IDs – this step is not mandatory as it would be possible to use implicit measures, however, I prefer to create my explicit measures.

No of Tickets Served =
COUNT ( Ticket[Ticket ID] )

Enter The Matrix

Once we have all of the above ready all we need to do is add the relevant fields to a matrix, like so:

A matrix visual containing Day Names, Hour Brackets and Number of Tickets served

Now we are talking!

But it still doesn’t look like a heatmap, right? I mean it does have the relevant numbers in the right places, but how can we start colour-coding those cells?

That’s when and where conditional formatting comes handy.

Conditional Formatting Options

Under the format panel down at the bottom there is a group of features called “Cell elements”, here we can set different conditional formatting rules for elements within a visual.

Conditional Formatting option for a matrix in Power BI

After enabling the Background Color option and clicking on the fX button a new pop-up window will be shown which will look like this by default:

Conditional Formatting Pop-up window in Power BI

Without going into all the details for this feature, I just show you what’s needed to recreate the heatmap visual we are so keen to replicate today.

Conditional Formatting settings for a Heatmap matrix visual

For the Minimum or Lowest value I want the background to be green – indicating not so busy periods.

For the Maximum or Highest value I want the background to be red – these are the busy periods.

And just like in a traffic light system in between the green and red values I would like to see yellow background.

Also, for null/zero/empty values I want to specify a color, grey – this is for weekend afternoons when the post office is closed. It should be visualised somehow differently, hence why I use an alternative color.

And with that our first step in the dataviz process will look like this:

First version of a Heatmap visual in Power BI

Not too bad, right? Well, it could be even better!

Next we have to replicate these steps for conditional formatting, but this time we need to format the font color…

Formatting steps are exactly the same – just keep in mind that we need to use the EXACT same colours for the font to be able to “hide” those numbers. I like to copy-paste the HEX code values to make my life easier.

And with that step completed this is how our matrix looks:

Heatmap Matrix visual in Power BI

Isn’t it amazing that any sort of Data Visualisation project can be replicated in Power BI?

I mean it’s not a built-in solution but with some smarts we can make it happen.

What’s Next?

While I was working on all of this I had another idea. While creating a heatmap “matrix” visual in Power BI is doable (as you saw that); how can we take this to the next level?

In other words, how can I create a report that has a look and feel of a well designed, aesthetically pleasing corporate style dashboard.

After fooling around a bit with design options I had one more idea about this data or dataviz – and it’s about how to provide insights or how to generate business value based on data.

I believe these additional ideas are representing some sort of “natural data evolution” or in other word BI maturity. I also like to go through a similar process with my reports – work on the data first, visualise it and then draw some insights from them.

So stay tuned for Part II and potentially Part III of this heatmap vizualisation in Power BI!


🙏Thanks for watching and if you liked this video please hit the 👍 button.

📢 Also be sure to subscribe to the channel so you never miss a new video.

Categories: Visualisation


Leave a Reply

Your email address will not be published. Required fields are marked *