Power BI Pez Chart

3 mins read

It’s well known that innovation often thrives during times of crisis. Data Viz is no different, with numerous memorable visualisations helping to tell the story of COVID19. One that stood out was this Pez chart, a specific type of heatmap named after the sweet that dispenses in small square blocks. Similar to a line chart, the Pez chart tracks changes in value over time but colour is used instead of the Y-axis to show differences in value.

There are no visuals specifically for creating Pez charts in the app source but it is possible to achieve a reasonable result with a default matrix and a few tweaks.

The data for this couldn’t be simpler, a two-column dataset with Date and Number of deaths is enough. Dropped into a matrix it looks like this:

You can apply conditional formatting to both the background and value text to create the basic blocks (the text disappears as it has the same format as the background):

To reduce the block size you can create an index for each row of the dataset in Power Query and use that for the column header (instead of the much wider date value). This narrows the blocks but throws up another sizing issue related to the number of deaths – days where the deaths are 99 or less are narrower than days that are 100+ (see the subtle change from day 11 to 12 below):

To represent the data faithfully each “Pez” needs to be exactly the same width. You can ensure that by applying a custom format to the daily deaths column so that the index always has the same number of characters for each day. This data didn’t rise above 999 deaths so “000” forced all values to be displayed with 3 digits (the conditional formatting has been removed on the text below so you can see the impact of the custom format):

To get even narrower blocks you can set the font size of both the values and the column header to DIN Light size 8 (and even narrower still if you increase the canvas size). Setting the column header text to be white effectively hides it, and making the grid white gives a slight separation between the blocks but you can experiment to see what works.

It’s a bit of a hack but produces a reasonable outcome with built-in visuals. It could be used as a colourful addition to a line chart, or potentially to highlight periods that crossed certain thresholds. It could also be used instead of a line chart where space is an issue – particularly where small multiples may be required to show how KPIs may have changed over time across multiple dimensions.

Download sample PBIX

Since writing this post a nice example has appeared from Microsoft made by Phil Seamark