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 
    )

     

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi Rahul_SC 

are you expecting something like:

Ranking =
    RANKX(
            ALLSELECTED('Table'[Carrier]), 
            [sum],,
            ASC,
            Dense
)
 
  • Rahul_SC's avatar
    Rahul_SC
    Helper IV
    3 years ago

    Hi FreemanZ ,

     

    Thanks for your response. But, I acutally can not use  "ALLSELECTED('Table'[Carrier])". This is because, If you see in my table (above image), I am ranking Carrier based on Total Samulation value within LaneID group (this is my requirement). 

    If I follow your Dax, then we would get global ranking of carrier without considering LaneID. 

     

    My dax (mentioned above) is working fine in the table and giving the required output. But the issue is as soon as I remove LaneID (column) from the table then it gives incorrect ranking (see below). 

     

     

    Can you suggest any other measure to calculate rank of carrier based on Total Simulation considering LaneID as group ? and, it should work even if LaneID (group) is not in the table. I want to do this because this l will use it to show bar graph to show value by carriers. The value will be calculated based on Ranking. If ranking changes then value also would change.