Raking for each hierarchy in Matrix

[Anany]

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:

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 =

CALCULATE(

RANKX( ALL( Products_Data[Product Name] ) , [TotalSales] , , DESC ) ,

ALL( Customer_Data[Customer Names] ) ,

ALLSELECTED( Regions_Table[City] ) ,

ALLSELECTED( Sales_Data[Channel] ) )

VAR _Results =

SWITCH( TRUE() ,

ISINSCOPE( Products_Data[Product Name] ) , _Product_Ranking ,

ISINSCOPE( Regions_Table[City] ) , _City_Ranking ,

ISINSCOPE( Customer_Data[Customer Names] ) , _Customer_Ranking ,

ISINSCOPE( Sales_Data[Channel] ) , _Channel_Ranking ,

BLANK() )

RETURN

_Results

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

[v-rongtiep-msft]

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.

[Anany]

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.

[v-rongtiep-msft · Accepted 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.

[Anany]

@v-rongtiep-msft  Thank you so much. It works really well.

[v-rongtiep-msft]

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.

[Anany]

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.