Forum Discussion
Group Ranking in Measure, and Bar chart
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 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
) + 0Hi 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.
Please try
RankMeasure =
RANKX (
CALCULATETABLE ( VALUES ( 'Table'[Carrier] ), ALLSELECTED ( 'Table'[Carrier] ) ),
[sum],
,
ASC,
DENSE
)
