Forum Discussion

bdehning's avatar
bdehning
Post Prodigy
3 years ago

Rank Measure

I have this measure    Rank Product = IF (     ISINSCOPE( 'InjuryCause'[Cause Grouping]),     RANKX (         CALCULATETABLE (             VALUES ( 'InjuryCause'[Cause Grouping]),        ...
Need Help
hnguy71's avatar
hnguy71
Super User
3 years ago

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
    )
)
bdehning's avatar
bdehning
Post Prodigy
3 years ago

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.