Forum Discussion
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....
Rahul_SC
I hope this time is correctAllocated_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
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
)
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
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.