Forum Discussion

Rahul_SC's avatar
Rahul_SC
Icon for Helper IV rankHelper 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
Icon for Helper IV rankHelper 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
Icon for Helper IV rankHelper 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%