Forum Discussion

Rahul_SC's avatar
Rahul_SC
Helper IV
3 years ago
Solved

Group Ranking in Measure, and Bar chart

Hi,   I have written dax for group ranking. It is working fine in table. The Ranking is dynamic. If I unselect  a value from slicer (carrier name), the ranking gets updated for the selected values....
Commands and Tips
DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    Rahul_SC 
    I hope this time is correct

    Allocated_Spend_New = 
VAR SelectedCarriers = ALLSELECTED ( 'Table'[Carrier] )
RETURN 
    SUMX ( 
        SUMMARIZE ( 'Table', 'Table'[LaneID], 'Table'[Carrier] ),
        VAR SumValue = [sum]
        VAR R1 = [Allocation % for R1]
        VAR R2 = [Allocation % for R2 Value]
        VAR R3 = [Allocation % for R3 Value]
        VAR RValue =
            SWITCH (
                RANKX(
                    SelectedCarriers,
                    COALESCE ( [sum], 99999999999 ),,
                    ASC,
                    Dense
                ),
                1, R1,
                2, R2,
                3, R3
            )
        RETURN   
            SumValue * RValue 
    )

     

Rahul_SC's avatar
Rahul_SC
Helper IV
3 years ago

Hi tamerj1 ,

 

The workaround is, I made a little tweak in Rankx (subtracted by 1) to link R1 slicer. But would like to know why this is giving rank (added by one)

 

 

Rahul_SC's avatar
Rahul_SC
Helper IV
3 years ago

Hi tamerj1 ,

 

sorry, but I have expericed that the Rank measure is not working fine. 

In slicer, if we select 3 carriers or less than 3 then Ranking is correct. 

 

But the moment, we select more than 3 carriers in the slicer then we get different Ranking (probalby global ranking). Rank1 does not come under any carrier. 

 

 

  • Rahul_SC's avatar
    Rahul_SC
    Helper IV
    3 years ago

    tamerj1 Thank you, now completely understood. 

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Rahul_SC 
    I hope this time is correct

    Allocated_Spend_New = 
VAR SelectedCarriers = ALLSELECTED ( 'Table'[Carrier] )
RETURN 
    SUMX ( 
        SUMMARIZE ( 'Table', 'Table'[LaneID], 'Table'[Carrier] ),
        VAR SumValue = [sum]
        VAR R1 = [Allocation % for R1]
        VAR R2 = [Allocation % for R2 Value]
        VAR R3 = [Allocation % for R3 Value]
        VAR RValue =
            SWITCH (
                RANKX(
                    SelectedCarriers,
                    COALESCE ( [sum], 99999999999 ),,
                    ASC,
                    Dense
                ),
                1, R1,
                2, R2,
                3, R3
            )
        RETURN   
            SumValue * RValue 
    )

     

  • Rahul_SC's avatar
    Rahul_SC
    Helper IV
    3 years ago

    Hi tamerj1 ,

     

    I would like to know why you have used [Coalesce] function in Rankx. Acutally, I am trying to use this dax in another coumn where Sumvalue is differenct column and Ranking is happening based on different column so, this dax is not working 

     

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Rahul_SC 

    Beacuse when placed inside the summary table, one of the produced values of [sum] is blank. Blank is is always the minimum value therefore always has the ascending rank 1. COALESCE replaces the blank with a high number "99999999" to return the highest ascending rank. This won't have an other effect and can be used with measures other than [sum] without any problem.