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 
    )

     

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. 

Rahul_SC's avatar
Rahul_SC
Helper IV
3 years ago

tamerj1 Thank you, now completely understood.