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

Hi tamerj1 ,

 

Thank you!

 

The 2nd table (right side) is giving the correct output. This is exactly what I want but there is one issue in the Rank Measure. It is not dynamic. It has fixed the Ranking. 

 

Let's say, if I remove any carrier from the slicer then the Ranking should be done again on the remaining carrier of the table. I have written the measure (Rank Measure_New) which is working correctly. See below when all the carriers are selected then MSC has rank 1. 

 

 

When I unselect MSC from the slicer then your Rank measure is not giving ranking dynamically, it has removed the Rank 1. RankMeasure_New is ranking correctly and giving rank on the remaining carrier dynamically.  This should also happen along with summarization of the table. 

 

 

 

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Rahul_SC 

Please try

RankMeasure =
RANKX (
CALCULATETABLE ( VALUES ( 'Table'[Carrier] ), ALLSELECTED ( 'Table'[Carrier] ) ),
[sum],
,
ASC,
DENSE
)

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