Assistance Needed: Creating Heatmap in Power BI

[manoj_0911]

Hello Power BI Community,

I'm currently working on transitioning my reports from Tableau to Power BI, and I'm facing challenges with creating a heatmap similar to what I had in Tableau.

In Tableau, I successfully built a heatmap that visualized data effectively. However, when attempting to replicate the heatmap in Power BI, I encountered some difficulties with sorting the rows and columns correctly.

Tableau Heatmap Screenshots:

Power BI Heatmap Screenshot:

Tableau Heatmap Calculations:

Power BI Heatmap Calculations:

-----------------------------------------

Weekday = WEEKDAY('IVR_SUBHOUR_V'[IXN_SUBHOUR_DATE], 2)

-----------------------------------------

Hour = HOUR('IVR_SUBHOUR_V'[IXN_SUBHOUR_DATE])

-----------------------------------------

Auth % = DIVIDE(SUM('IVR_SUBHOUR_V'[AUTH_COUNT]), SUM('IVR_SUBHOUR_V'[TOTAL_CALLS]), 0)

-----------------------------------------

Calls Transferred % = DIVIDE([Calls Transferred], SUM('IVR_SUBHOUR_V'[TOTAL_CALLS]), 0)

-----------------------------------------

Day_of_Week = FORMAT('AGENT_SUBHOUR_V'[IXN_SUBHOUR_DATE], "dddd")

-----------------------------------------

Day_of_Week_Order =
SWITCH(
'AGENT_SUBHOUR_V'[Day_of_Week],
"Sunday", 1,
"Monday", 2,
"Tuesday", 3,
"Wednesday", 4,
"Thursday", 5,
"Friday", 6,
"Saturday", 7,
0
)

-----------------------------------------

Hour_of_Day = HOUR('AGENT_SUBHOUR_V'[IXN_SUBHOUR_DATE])

-----------------------------------------

The main issue I'm experiencing in Power BI is with sorting the rows and columns properly. Specifically, I want the following ordering:

- Columns: From 12:00 AM to 11:00 PM
- Rows: From Sunday to Saturday (in this exact order)

However, despite my efforts, the rows and columns in Power BI are not sorting correctly according to my specifications.

Could you please provide guidance or suggestions on how to ensure the correct ordering of rows and columns in Power BI for the heatmap? Any insights or advice would be greatly appreciated.

Thank you for your assistance!

Best regards,
Manoj Prabhakar

[v-kaiyue-msft · Accepted solution]

Hi @manoj_0911 ,

1.Rows: From Sunday to Saturday

Since power bi sorts alphabetically by default, you need to create a new table to sort from Sunday to Saturday. Create a calculation table and write a dax expression:

True =

SUMMARIZE(

    'Table','Table'[Day Name],"min",MINX(FILTER(ALL('Table'),'Table'[Day Name]=EARLIER('Table'[Day Name])),[Day of Week]))

Select the Day Name column in the calculation table and set it to sort by the min column.

Create a relationship between the calculation table and the table you want to use so that you can use the Day Name of the calculation table to display the data correctly in the report.

2.Columns: From 12:00 AM to 11:00 PM

It is recommended that you create a new column identical to [IXN_SUBHOUR_DATE] and select the short time format in the format drop-down menu instead of using the hour function. This way power bi will sort by 12:00 AM to 11:00 PM by default.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.