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. 

 

Ranking =
    RANKX(
        FILTER(
            ALLSELECTED('Table'), 'Table'[LaneID] = MAX('Table'[LaneID])),
            [sum],,
            ASC,
            Dense)
 
This is the output of the table
 

 

It does not work the moment, I remove LaneID. How to make it work even, I do not keep Lane ID in table ?

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 
    )

     

17 Replies

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

       

       

       

    • Rahul_SC's avatar
      Rahul_SC
      Helper IV
      3 years ago

      Hi tamerj1 ,

       

      I have the table 1 which is correct. I want to show this data like table 2. But we can see in the column value (Allocated Spend) is not same in both the tables. The table 2 should summarize the value for each carrier but it seems not working. 

       

      I would like to show the value like in table 2. 

       

       

      • Rahul_SC's avatar
        Rahul_SC
        Helper IV
        3 years ago

        To explain fulther my question - 

         

        LaneID, Carrier, TotalSimulation is a part of physical table.

         

        allocated spend is a measure. It is calculated on % basis. like 

         

        IF([RankMeasure] = 1,
                        [TotalSimulation]*60%,
                    IF([RankMeasure] = 2,
                        [TotalSimulation]*15%,
                    IF([RankMeasure]=3,
                        [TotalSimulation]*20%
  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Rahul_SC 
    I hope this is what you're looking for; please refr to attached sample file

    RankMeasure = 
RANKX(
    CALCULATETABLE (
        VALUES ( 'Table'[Carrier] ), 
        ALLEXCEPT ( 'Table', 'Table'[LaneID] )
    ),
    [sum],,
    ASC,
    Dense
)
    Allocated_Spend_New = 
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 (
            [RankMeasure],
            1, R1,
            2, R2,
            3, R3
        )
    RETURN
        SumValue * RValue
) + 0
    • 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
        )