Forum Discussion
Rank Measure
)
bdehning ,
In this case, you need a sort of ... tiebreaker. Try something like this...
VAR _TieBreaker =
IF(
ISINSCOPE('InjuryCause'[Cause Grouping]),
VAR _cRecord = MAX('InjuryCause'[Cause Grouping])
VAR _tmpRank = COUNTROWS( FILTER(ALL('InjuryCause'), 'InjuryCause'[Cause Grouping] >= _cRecord ))
RETURN _tmpRank
)
RETURN
IF(
ISINSCOPE( 'InjuryCause'[Cause Grouping]),
RANKX (
CALCULATETABLE (
VALUES ( 'InjuryCause'[Cause Grouping]),
ALLSELECTED ( 'InjuryCause'[Cause Grouping] )
),
[Count of Total Gross Incurred] + DIVIDE(_TieBreaker, 100),
,
DESC,
DENSE
)
)Ok excuse my novice to this, what is cRecord and why the divide?
All I want to do it use the Count of Total Gross Incurred to establish the rank and then use Sum of Total Gross Incurred for each Cause Group to break the tie.
bdehning ,
cRecord is just a variable name. You can call it whatever you want, but i'm labeling it as cRecord to indicate that it is the current record on the visual.
The divide is used to convert a whole number into a decimal number. The tiebreaker looks at all visible output and returns a tiebreaker score starting from 1 and upwards until your last visible record.
If you break down the output it would be something simlar (considering lowest count is ranked 1):
1 + 1/100 = Rank 12 + 2/100 = Rank 23 + 3/100 = Rank 34 + 4/100 = Rank 45 + 5/100 = Rank 55 + 6/100 = Rank 65 + 7/100 = Rank 75 + 8/100 = Rank 85 + 9/100 = Rank 9Having a hard time in getting this to work . We would need to use the Sum of Total Gross Incurred for each Cause Group to break the tie.
[Count of Total Gross Incurred] + DIVIDE(_TieBreaker, 100),
How could the Divide be swapped out to use the Sum of Total Gross Incurred for each Cause Group