Forum Discussion

Rahul_SC's avatar
Rahul_SC
Icon for Helper IV rankHelper 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
Icon for Helper IV rankHelper IV
3 years ago

Hi tamerj1 ,

 

Excellent,

 

Yes, it is perfect.

 

Just need to modify switch function a little. Actullay, if we are making changes in R2 slicer then it is calulating for R1 in the table. When we change in R3 slicer then it is calculating for R2 in table. 

R1 slicer is not linked. 

 

 

Rahul_SC's avatar
Rahul_SC
Icon for Helper IV rankHelper 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
    Icon for Helper IV rankHelper IV
    3 years ago

    tamerj1 Thank you, now completely understood. 

  • Rahul_SC's avatar
    Rahul_SC
    Icon for Helper IV rankHelper 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. 

     

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity 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
    Icon for Helper IV rankHelper 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
    Icon for Community Champion rankCommunity 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.