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

tamerj1 ,

 

Now, ranking is correct (it is dynamic) but the summarized table is incorrect. At total, the value is correct (Allocated Spend_New) but in the filter context, it is not. 

 

I have doing if Rank1 (100%), Rank 2 or 3 then 0% of Total Simulation. 

 

 

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.