Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Recently I've tried to calculate the rank for each column in the matrix hierarchy I'm able to calculate up to 2 levels but when there are more than 2 levels or columns involved it gives an incorrect output.
I did some r&d and found this solution by Harsh he is calculating the ranking for 4 levels/column but when I try to remove the city column and change the dax measure it gives incorrect output.
Can anyone help me out with what I'm doing wrong?
I am attaching below the pbix with sample data and existing dax measure.
Thanks!
Exisitng dax measure:
I want to remove the city from this hierarchy (There will be only 3 levels - Channel, Customer Names, Product Name)
I've removed the city from the hierarchy & modified the measure accordingly but still, it gives incorrect output.
Gdrive link: - https://drive.google.com/file/d/1wEJb9DdRHqUMNTX_YTE-ZbeJlSdktKcn/view?usp=sharing
Solved! Go to Solution.
Hi @Anany ,
You can try to use the city column in the measure. When you remove the city column, the rankx still works well. Even if you add it, it still works.
Ranking - Harsh =
VAR _Channel_Ranking =
CALCULATE(
RANKX( ALL( Sales_Data[Channel] ) , [TotalSales] , , DESC ) ,
ALL( Customer_Data[Customer Names] ) ,
ALL( Regions_Table[City] ) ,
ALL( Products_Data[Product Name] ) )
VAR _Customer_Ranking =
CALCULATE(
RANKX( ALL( Customer_Data[Customer Names] ) , [TotalSales] , , DESC ) ,
ALL( Regions_Table[City] ) ,
ALL( Products_Data[Product Name] ) ,
ALLSELECTED( Sales_Data[Channel] ) )
VAR _City_Ranking =
CALCULATE(
RANKX( ALL( Regions_Table[City] ) , [TotalSales] , , DESC ) ,
ALL( Products_Data[Product Name] ) ,
ALL( Customer_Data[Customer Names] ) ,
ALLSELECTED( Sales_Data[Channel] ) )
VAR _Product_Ranking =
RANKX(ALLSELECTED(Products_Data[Product Name]),CALCULATE(SUM(Sales_Data[Total Revenue])),,DESC)
VAR _Results =
SWITCH( TRUE() ,
ISINSCOPE( Products_Data[Product Name] ) , _Product_Ranking ,
ISINSCOPE( Customer_Data[Customer Names] ) , _Customer_Ranking ,
ISINSCOPE(Regions_Table[City]),_City_Ranking,
ISINSCOPE( Sales_Data[Channel] ) , _Channel_Ranking ,
BLANK() )
RETURN
_Results
You can compare the difference between the two measures and the two visuals.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anany ,
Please modify you measure.
Ranking - Harsh =
VAR _Channel_Ranking =
CALCULATE(
RANKX( ALL( Sales_Data[Channel] ) , [TotalSales] , , DESC ) ,
ALL( Customer_Data[Customer Names] ) ,
ALL( Regions_Table[City] ) ,
ALL( Products_Data[Product Name] ) )
VAR _Customer_Ranking =
CALCULATE(
RANKX( ALL( Customer_Data[Customer Names] ) , [TotalSales] , , DESC ) ,
ALL( Regions_Table[City] ) ,
ALL( Products_Data[Product Name] ) ,
ALLSELECTED( Sales_Data[Channel] ) )
VAR _City_Ranking =
CALCULATE(
RANKX( ALL( Regions_Table[City] ) , [TotalSales] , , DESC ) ,
ALL( Products_Data[Product Name] ) ,
ALL( Customer_Data[Customer Names] ) ,
ALLSELECTED( Sales_Data[Channel] ) )
VAR _Product_Ranking =
RANKX(ALLSELECTED(Products_Data[Product Name]),CALCULATE(SUM(Sales_Data[Total Revenue])),,DESC)
VAR _Results =
SWITCH( TRUE() ,
ISINSCOPE( Products_Data[Product Name] ) , _Product_Ranking ,
ISINSCOPE( Customer_Data[Customer Names] ) , _Customer_Ranking ,
ISINSCOPE( Sales_Data[Channel] ) , _Channel_Ranking ,
BLANK() )
RETURN
_Results
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-rongtiep-msft
can you please explain you have used the city column in the measure when you are not including that particular column in the matrix? If I remove the city from the measure it gives the wrong output.
Hi @Anany ,
You can try to use the city column in the measure. When you remove the city column, the rankx still works well. Even if you add it, it still works.
Ranking - Harsh =
VAR _Channel_Ranking =
CALCULATE(
RANKX( ALL( Sales_Data[Channel] ) , [TotalSales] , , DESC ) ,
ALL( Customer_Data[Customer Names] ) ,
ALL( Regions_Table[City] ) ,
ALL( Products_Data[Product Name] ) )
VAR _Customer_Ranking =
CALCULATE(
RANKX( ALL( Customer_Data[Customer Names] ) , [TotalSales] , , DESC ) ,
ALL( Regions_Table[City] ) ,
ALL( Products_Data[Product Name] ) ,
ALLSELECTED( Sales_Data[Channel] ) )
VAR _City_Ranking =
CALCULATE(
RANKX( ALL( Regions_Table[City] ) , [TotalSales] , , DESC ) ,
ALL( Products_Data[Product Name] ) ,
ALL( Customer_Data[Customer Names] ) ,
ALLSELECTED( Sales_Data[Channel] ) )
VAR _Product_Ranking =
RANKX(ALLSELECTED(Products_Data[Product Name]),CALCULATE(SUM(Sales_Data[Total Revenue])),,DESC)
VAR _Results =
SWITCH( TRUE() ,
ISINSCOPE( Products_Data[Product Name] ) , _Product_Ranking ,
ISINSCOPE( Customer_Data[Customer Names] ) , _Customer_Ranking ,
ISINSCOPE(Regions_Table[City]),_City_Ranking,
ISINSCOPE( Sales_Data[Channel] ) , _Channel_Ranking ,
BLANK() )
RETURN
_Results
You can compare the difference between the two measures and the two visuals.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anany ,
It works well! Could you please use another matrix on a new page.
There was something wrong with the date slicer. Please have a check.
If I have misunderstood your meaning, please provide more details with your desired output.
How to Create a Dynamic Rank in Matrix with Hierarchy?
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-rongtiep-msft
Thank you for giving it a try however my desired output is to calculate the rank for each column/hierarchy.
On the channel level, your ranking works well but if I go to the second and third level that customer names , Product Names it gives the wrong output.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |