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 
Will have to use all in one measure.

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,
                    [sum],,
                    ASC,
                    Dense
                ),
                1, R1,
                2, R2,
                3, R3
            )
        RETURN
            SumValue * RValue
    ) + 0

Rahul_SC's avatar
Rahul_SC
Helper 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
    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. 

       

       

      • 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 
    )