Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

 

 

 

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    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.

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous 

      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. 

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous 

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

        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.