Skip to content
Home

KPI dashboards in Power BI part 2: Building the dashboard - Heatmaps

Published by Simon Jeffery on

 

Introduction 

This is the second part in the series of building a KPI dashboard in Power BI.

We have created our top level KPI's - the KPI cards and the table with the sparklines.

Now a user may want to look at the possible drivers of performance behind these top level KPI's.

In this part 2 of the blog, we will show you how to drill through into a lower level of the data and examine these drivers by using heatmaps.

Using heatmaps

A Power BI heatmap demonstrates the density of data on the heatmap.

KPI variances against the targets are represented in a matrix as colours. 

You can either show the variances using rules and gradients.

Rules show absolute colours:

  • If the variance is favourable we will use a green colour coding
  • if the variance is unfavourable we will use a red colour coding

Gradients show a gradual change in colours from green (favourable) through the red (unfavourable):

  • The stronger the green colour, the stronger the favourable variance
  • The stronger the red colour, the stronger the unfavourable variance

The creation of a heatmap is quite well hidden:

  • Create a matrix table
  • Go to Cell Elements within formatting 
  • Use conditional formatting on the background colour to create the heatmap effect

Now, it is over to Power BI to set up the Heatmaps:

Set up a new tab and the drill through

We will copy the KPI card tab over to a new tab and link the two for drill through purposes.

1.  Right click on the tab name and select Duplicate Page 
2.  Rename the tab New KPI card DT (DT = drill through) and move the tab next to New KPI card
3.  Go to the New KPI card
  • In the Visualization Pane in the Drill though section at the bottom:

    Drag the field Revenues [Share Class] in to the box Add drill-through fields here

  • Turn Cross-report - On

4.  Go into the tab New KPI card DT and repeat step 4 for this tab
5.  Test the drill through
  • Go into the tab New KPI card
  • Right-click on one of the Share Class entries in one of the tables
  • Select the Drill through option and then the tab New KPI card DT

  • Select the tab you have drilled though into - New KPI card DT
  • You should see the screen as below – all the filters should have picked up your drill through selections

 

Set up the canvas for the two heat maps

1.  Go to the tab New KPI card DT
2.  Delete all of the KPI cards
3.  Move the two tables over to the left of the canvas
 

Convert the Revenues table into the first heat map – applying rules

1.  Go to the tab New KPI card DT
2.  Convert the Revenues visual to a Matrix in the Report pane

3.  In the Build visual section, add in the following fields:
  • Rows - Revenues [Salesperson]
  • Columns - Dates [Year Month]
  • Values - Revenues [Metric Amount Revenues Variance k]

Note: The measure Metric Amount Revenues Variance k is built as follows:

Metric Amt Revenues Variance k = ([Metric Amount Revenues] - [Metric Amount Tgt Revenues]) / 1000

 

4.  In the Format visual section, apply the following formatting changes:
From the Visual section:
 
  • Increase the font sizes
  • Values | Values | Font – Sergui UI Bold 15
  • Column headers | Text | Font – Sergui UI Bold 15
  • Row headers | Text | Font – Sergui UI Bold 15
Turn the totals off :
 
  • Column subtotals – Off
  • Row subtotals – Off
The heat maps are generated in this section:
  • Cell elements | Apply settings to – Metric Amount Revenues Variance k
  • Cell elements | Background color – On

Select the conditional formatting fx button

Complete the Background color box as follows:
  •  Format style – Rules
  • Apply to – Values only
  • What field should we base this on? – Revenues [Variance]
  • Rule 1:
    • If value – >= 0 Number
    • And – <= 100 Percent
    • Then – green #04900B
    • +New rule
  • Rule 2:
    • If value – >= 0 Percent
    • And – <= 0 Number
    • Then – red #D82C20
    • OK




From the General section:
 
  • Title | Text …..
  • Select the Conditional formatting fx button
  • In the drop down box select as follows:
    • Format style - Field value
    • What field should we base this on? – Selected Share Class DD Revenues Header
    • Click OK
Note: The measure Selected Share Class DD Revenues Header has been created as follows:
Selected Share Class DD Revenues Header = "Revenues Variance (£k) by " & SELECTEDVALUE('Revenues' [Share Class]) & " - " & SELECTEDVALUE('Metric Current YTD'[Metric Selected])
 
  • Change the font size to 15
  • Title | Text | Font – Sergui UI Bold 15
  • Convert the Profit margin % table into the second heat map – applying grading
  • Go to the tab New KPI card DT                                                                                                                                                                                                                                                                                            
5.  Convert the Profit margin % visual to a Matrix in the Report pane
 
6.  In the Build visual section, add in the following fields:
  • Rows - Revenues [Salesperson]
  • Columns - Dates [Year Month
  • Values - Revenues [Metric Amount Profit Mgn Variance]
  • The measure Metric Amount Profit Mgn Variance is built as follows:
Metric Amt Profit Mgn Variance = [Metric Amount Profit Margin] - [Metric Amount Tgt Profit Margin]
 
 
7.  In the Format visual section, apply the following formatting changes:
From the Visual section:
 
  • Increase the font sizes
    • Values | Values | Font – Sergui UI Bold 15
    • Column headers | Text | Font – Sergui UI Bold 15
    • Row headers | Text | Font – Sergui UI Bold 15 
  • Turn the totals off :
    • Column subtotals – Off
    • Row subtotals – Off
  • The heat maps are generated in this section:
    • Cell elements | Apply settings to – Metric Amount Revenues Variance k
    • Cell elements | Background color – On
    • Select the conditional formatting fx button
  • Complete the Background color box as follows:
    • Format style – Gradient
    • Apply to – Values only
    • What field should we base this on? – Revenues [Variance]
    • How should we format empty values? – As zero
    • Minimum – Lowest value
    • Minimum – red #D82C20
    • Maximum – Highest value
    • Maximum – green #04900B
    • Add a middle color – Leave blank
    • OK

 

 

From the General section:
    • Title | Text …..
    • Select the Conditional formatting fx button
 
  • In the drop down box select as follows:
    • Format style - Field value   
    • What field should we base this on? – Selected Share Class DD Profit Margin Header
    • Click OK
 
Note: The measure Selected Share Class DD Profit Margin Header has been created as follows:
Selected Share Class DD Profit Margin Header = "Profit Margin %  Variance by " & SELECTEDVALUE('Revenues' [Share Class]) & " - " & SELECTEDVALUE('Metric Current YTD'[Metric Selected])
 
  • Change the font size to 15
    • Title | Text | Font – Sergui UI Bold 15

Your second heatmap should look like this:

Conclusion 

In parts 1 and 2 of this blog, we have now looked at how to build KPI cards, sparklines and heatmaps.

For part 3 of the blog, we will be transferring to the Power BI service (powerbi.com) and looking at KPI Scorecards

Good luck with your heatmaps !!!

THE INSIGHTS+MODELLING TEAM

 

 

DISCLAIMER

The publication has been prepared for general guidance on matters of interest only, and does not constitute professional advice. You should not act upon the information contained in this publication without obtaining specific professional advice. No republication or warranty (express or implied) is given as to the accuracy or completeness of the information contained in this publication, and, to the extent permitted by law. Theta Global Advisors LLP, employees and agents do not accept or assume any liability, responsibility or duty of care for any consequences of you or anyone else acting, or refraining to act, in reliance on the information contained in this publication or for any decision based upon it. Ó 2023 Theta. All rights reserved. Not for further distribution without the permission of Theta.