Implementing a Table Heat Map in Power BI

A Heat map is used to visualize the density of data using colors where highest data value is displayed through dark heated color, and same color turns in light color for lowest values.

In Power BI, you can convert a Matrix table into table Heat map, you just need to apply some conditional formatting in matrix table.

Lets see here we have a sample data set of Sales by Month and year.

Year MonthNo Month Amount
2013 1 Jan 5000
2013 2 Feb 6000
2013 3 Mar 4500
2013 4 Apr 5000
2013 5 May 2000
2013 6 Jun 5000
2013 7 Jul 3000
2013 8 Aug 5643
2013 9 Sep 4500
2013 10 Oct 1800
2013 11 Nov 1300
2013 12 Dec 5000
2014 1 Jan 3789
2014 2 Feb 4600
2014 3 Mar 4500
2014 4 Apr 6000
2014 5 May 7844
2014 6 Jun 3400
2014 7 Jul 2000
2014 8 Aug 5000
2014 9 Sep 9795
2014 10 Oct 7855
2014 11 Nov 2500
2014 12 Dec 9000
2015 1 Jan 5000
2015 2 Feb 5000
2015 3 Mar 6000
2015 4 Apr 4632
2015 5 May 5079
2015 6 Jun 6300
2015 7 Jul 8686
2015 8 Aug 2000
2015 9 Sep 5000
2015 10 Oct 6000
2015 11 Nov 5444
2015 12 Dec 1000
2016 1 Jan 7555
2016 2 Feb 3000
2016 3 Mar 5000
2016 4 Apr 9654
2016 5 May 2999
2016 6 Jun 3000
2016 7 Jul 6000
2016 8 Aug 9755
2016 9 Sep 5333
2016 10 Oct 9474
2016 11 Nov 5756
2016 12 Dec 3958
2017 1 Jan 5000
2017 2 Feb 6555
2017 3 Mar 2995
2017 4 Apr 1000
2017 5 May 8000
2017 6 Jun 1000
2017 7 Jul 4822
2017 8 Aug 7888
2017 9 Sep 4885
2017 10 Oct 3763
2017 11 Nov 3000
2017 12 Dec 5000

Lets take this data into matrix visual and create a simple sales report to show Sales done by Month and Year.



Now we will format this matrix table and convert it into table Heat map.

Lets go to the formatting Tab and set Style type to None. It will remove row formatting such as alternate row colors and borders.



Now in Conditional formatting, enable Background color and click on Advance controls.

Once you click on Advance Controls, a Background color dialog box opens.

Here you can set a color based on minimum and maximum value.

Now its totally depends on you, how do you set the color formatting. You can go either with default color formatting in our case such as light blue color for lowest data values and dark blue color for highest data values to receive the viewer attention or you can set different color such as red color for lowest data value and green color for highest value.

Now take a look on matrix, and you will see the background color of highest value is shown in dark blue color and lowest value is shown in light blue color box, so the color format will vary light blue to dark dark blue based for lowest value to maximum value.

Now in case, if you want to distribute data into three colors such as color for minimum, center and maximum values then you can enable Diverging.

Enabling Diverging allows you to set color for minimum, center, and maximum values as shown below.

Lets change the colors for minimum values to Red, for center values to light Red and maximum values to Green.

After making these changes click on OK button.

You can see, now matrix is converted into Heat map, where values having dark green color background indicates highest values, and less green color indicates less highest values while dark Red color indicates lowest values and light red color indicates some ranges of values between lowest and highest values.

So, it becomes easy for users to focus on either dark red color to see the lowest recorded sales for any year, month or focus on dark green color to see the highest recorded sales for any year, month.

If you do not want to show sales amount on visual, you can do it by setting up a conditional formatting for font’s color as shown below.

In Fields Tab, right click on Amount then navigate to Conditional formatting and select Font Color




Now you will see a Font color dialog box opens, you just need to set same color as you did for background color.

Next click on OK button.

Now you can see, matrix looks more like a Heat Map and more interactive than previous.


Also Read..

Show/Hide Measures in Line chart visuals using slicer in Power BI

Creating a calendar table

Creating a relationship between tables when both table have duplicate values

Visual Zoom Sliders Feature in Power BI

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window

 181 total views,  3 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.