Group Ranking in Measure, and Bar chart

[Rahul_SC]

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 ?

[tamerj1]

@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]

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]

@Rahul_SC 

Please try

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

[Rahul_SC]

@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]

@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]

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]

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]

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 · Accepted solution]

@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]

Hi @tamerj1 ,

I would like to know why you have used [Coalesce] function in Rankx. Acutally, I am trying to use this dax in another coumn where Sumvalue is differenct column and Ranking is happening based on different column so, this dax is not working 

[tamerj1]

@Rahul_SC 

Beacuse when placed inside the summary table, one of the produced values of [sum] is blank. Blank is is always the minimum value therefore always has the ascending rank 1. COALESCE replaces the blank with a high number "99999999" to return the highest ascending rank. This won't have an other effect and can be used with measures other than [sum] without any problem. 

[Rahul_SC]

@tamerj1 Thank you, now completely understood. 

[tamerj1]

Hi @Rahul_SC 

what should be the result considering the same example?

[Rahul_SC]

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]

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%

[FreemanZ]

hi @Rahul_SC 

are you expecting something like:

Ranking =

    RANKX(

            ALLSELECTED('Table'[Carrier]), 

            [sum],,

            ASC,

            Dense

)

[Rahul_SC]

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.