topic Re: Group Ranking in Measure, and Bar chart in DAX Commands and Tips

Group Ranking in Measure, and Bar chart

Rahul_SC — Sun, 05 Feb 2023 00:03:35 GMT

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 ?

Re: Group Ranking in Measure, and Bar chart

FreemanZ — Sun, 05 Feb 2023 00:55:17 GMT

hi Rahul_SC

are you expecting something like:

Ranking =

RANKX(

ALLSELECTED('Table'[Carrier]),

[sum],,

ASC,

Dense

)

Re: Group Ranking in Measure, and Bar chart

Rahul_SC — Sun, 05 Feb 2023 08:37:49 GMT

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.

Re: Group Ranking in Measure, and Bar chart

tamerj1 — Sun, 05 Feb 2023 18:04:39 GMT

Hi Rahul_SC

what should be the result considering the same example?

Re: Group Ranking in Measure, and Bar chart

Rahul_SC — Sun, 05 Feb 2023 18:20:49 GMT

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.

Re: Group Ranking in Measure, and Bar chart

Rahul_SC — Sun, 05 Feb 2023 18:34:21 GMT

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%

Re: Group Ranking in Measure, and Bar chart

tamerj1 — Mon, 06 Feb 2023 11:58:25 GMT

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

Re: Group Ranking in Measure, and Bar chart

Rahul_SC — Tue, 07 Feb 2023 14:32:37 GMT

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.

Re: Group Ranking in Measure, and Bar chart

tamerj1 — Tue, 07 Feb 2023 14:43:25 GMT

Rahul_SC

Please try

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

Re: Group Ranking in Measure, and Bar chart

Rahul_SC — Tue, 07 Feb 2023 14:55:06 GMT

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.

Re: Group Ranking in Measure, and Bar chart

tamerj1 — Tue, 07 Feb 2023 18:06:35 GMT

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

Re: Group Ranking in Measure, and Bar chart

Rahul_SC — Tue, 07 Feb 2023 19:21:21 GMT

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.

Re: Group Ranking in Measure, and Bar chart

Rahul_SC — Tue, 07 Feb 2023 19:37:32 GMT

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)

Re: Group Ranking in Measure, and Bar chart

Rahul_SC — Wed, 08 Feb 2023 08:58:37 GMT

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.

Re: Group Ranking in Measure, and Bar chart

tamerj1 — Wed, 08 Feb 2023 13:33:27 GMT

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 )

Re: Group Ranking in Measure, and Bar chart

Rahul_SC — Sun, 12 Feb 2023 06:56:38 GMT

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

Re: Group Ranking in Measure, and Bar chart

tamerj1 — Sun, 12 Feb 2023 11:43:24 GMT

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.

Re: Group Ranking in Measure, and Bar chart

Rahul_SC — Sun, 26 Feb 2023 18:28:13 GMT

tamerj1 Thank you, now completely understood.