Heat Map grouping issue.

[Anonymous]

Hi Team,

Good day.

I am currently working on a Heat Map, and I would like to share my requirements and seek your assistance.

The Heat Map is configured with two categorical fields (F1 and F2) along with one measure (M1). I also wish to implement a color gradient for these categorical fields based on another measure (M2). While I have successfully integrated these components, I am encountering an issue with the sorting order when drilling down from level 1 to level 2.

Example:

1. In the Heat Map, I have included F1 and F2 as categorical fields, with M1 and M2 as measures on the Category axis. I have enabled the drill-down option for F1 and F2. Unfortunately, I cannot add F2 to the details axis as this would prevent the color gradient from functioning correctly.
2. In the Conditional Formatting settings, I have adjusted my formula based on M2 for F1 and F2. However, the visual displays the sorting order in descending by M1, rather than maintaining the intended order based on F1 and F2.

Could you please provide guidance on how to resolve this sorting issue without altering the order when drilling down? Any alternative solutions or recommendations would be greatly appreciated.

Thank you for your assistance.

[bhanu_gautam]

@Anonymous , Try below steps

Sort by Column
Select the field (F1 or F2) in the Fields pane.
Go to the Column tools tab.

Click on "Sort by Column" and select the column that maintains the intended order.

Create a new column in your data model with a numerical value for each category in the desired order.
# Example in Power Query (M language)
Table.AddColumn(#"PreviousStep", "CustomSortOrder", each if [F1] = "Category1" then 1 else if [F1] = "Category2" then 2 else 3)

Use this new column to sort your categorical fields.
Maintain Sort Order in Drill-Down
Set the sort order for each level of the hierarchy individually.
Ensure that the sort order is consistent across all levels.

Conditional Formatting
Go to the Format pane.
Select Conditional Formatting.
Set the rules based on M2.