cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anany
Helper I
Helper I

Raking for each hierarchy in Matrix

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

 

Anany_0-1655811343509.png

 

 

 

 

1 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

vpollymsft_0-1655966531615.png

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.

 

View solution in original post

6 REPLIES 6
v-polly-msft
Community Support
Community Support

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

vpollymsft_0-1655965105884.png

 

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-polly-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

vpollymsft_0-1655966531615.png

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.

 

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

v-polly-msft
Community Support
Community Support

Hi @Anany ,

It works well!  Could you please use another matrix on a new page.

vpollymsft_0-1655953937330.png

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-polly-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. 

Anany_0-1655959897508.png

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors