Creating a Heatmap in Power BI Report Builder
This blog demonstrates how to create a Heatmap in Power BI Report Builder. Heat Map is a custom visualization to show the data numbers through presentation or visual. The heat map will show the highest data density on one specific set of the area through dark heated color, and others will have the same heat as the highest value.
Here are the simple steps that will help to create a Heatmap in Power BI Report Builder.
Step1: Open Power bi report builder.
Step 2: To add a power bi dataset connection, left-click on the ‘Data Sources’ option.
Step 3: Select the required dataset that have been already published in the workspace and click ‘select’.
Step 4: To add dataset, right-click on ‘Datasets’ and select ‘Add Dataset’
Step 5: Select the required data source and then click the option ‘Query Designer’.
Step 6: The DAX query must be pasted into the query designer. Here are a few simple methods to quickly get the DAX query.
Step 7: Open the relevant Power BI dataset and select the data that is needed on a table visual.
Step 8: Select ‘Performance analyzer’ from the view tab, then click ‘start recording, refresh visuals’, and expand the table by clicking the “+” sign.
Step 9: To copy the query select the ‘Copy query’ option.
Step 10: A popup screen will appear when the ‘Query Designer’ option is selected.
Step 11: Select the ‘Design Mode’ Option and paste the DAX query which is copied.
Step 12: Now modify the copied DAX Query to retrieve all data rather than only the top 502 records.
// DAX QueryDEFINEVAR __DS0Core = SUMMARIZECOLUMNS(ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Orders'[Category], 'Orders'[State], 'Orders'[Segment]), "IsGrandTotalRowTotal"),"SumProfit", CALCULATE(SUM('Orders'[Profit])))VAR __DS0PrimaryWindowed =
TOPN(502,__DS0Core,
[IsGrandTotalRowTotal],0,'Orders'[Category],1,'Orders'[State],1,'Orders'[Segment],1)EVALUATE __DS0PrimaryWindowed ORDER BY [IsGrandTotalRowTotal] DESC, 'Orders'[Category], 'Orders'[State], 'Orders'[Segment]
Step 13: Change EVALUATE __DS0PrimaryWindowed to EVALUATE __DS0Core to get all records in the above DAX query and then click ‘Ok’.
// Modified DAX queryDEFINEVAR __DS0Core =SUMMARIZECOLUMNS(ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Orders'[Category], 'Orders'[State], 'Orders'[Segment]), "IsGrandTotalRowTotal"),"SumProfit", CALCULATE(SUM('Orders'[Profit])))EVALUATE __DS0CoreORDER BY[IsGrandTotalRowTotal] DESC, 'Orders'[Category], 'Orders'[State], 'Orders'[Segment]
Step 14: Left-click the mouse on the white space and select insert->Map.
Step 15: Select New layer Wizard icon to choose the required map layer.
Step 16: Choose the Map Gallery and the USA by State Inset, or you can choose the specific type you prefer.
Step 17: We can edit the map size as we need (Optional).
Step 18: Select the required Map you need and select Next.
Step 19: Select the Required Dataset.
Step 20: Select the STATENAME in the Spatial Dataset Fields and the matching field in the Analytical Dataset Fields (I have a state column in my dataset that contains State names, so I chose it as the Matching Field) click Next.
Step 21: Select the column to view in the legend, then the color rule we require. If a label is required, check the box “Display lables” and specify the data field to be displayed.
Output of the Report:
The Heatmap for Profit by State is created, like this the Heatmap can be made based on the requirement.
About Us
Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well known for superior delivery.
Website : https://bi3technologies.com/
Follow us on,
LinkedIn : https://www.linkedin.com/company/bi3technologies
Instagram : https://www.instagram.com/bi3technologies/
Twitter : https://twitter.com/Bi3Technologies
