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

Working on it.  Do cRecord and tmpRank need to be columms or can they be measures?

hnguy71's avatar
hnguy71
Super User
3 years ago

bdehning since you're not using the tiebreaker variable, you can get rid of it all

 

IF(
    ISINSCOPE( 'InjuryCause'[Cause Grouping]),
    RANKX (
        CALCULATETABLE (
            VALUES ( 'InjuryCause'[Cause Grouping]),
            ALLSELECTED ( 'InjuryCause'[Cause Grouping] )
        ),
        [Count of Total Gross Incurred] + [Sum of Total Gross Incurred],
        ,
        DESC,
        DENSE
    )
)
  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago

    I would need a tie breaker though as original data from Count only is   

    1

    2

    3

    4

    5

    5

    5

    5

    5 

     

    I ran the above and it ran and gave results of ranking I was looking,  but is it only adding the Sum to the count and then ranking and we only got lucky?     I do need the [Sum of Total Gross Incurred] as tie breaker if Count results in ties.   

      

  • hnguy71's avatar
    hnguy71
    Super User
    3 years ago

    bdehning that's correct.

     

    You need both the sum and the count. One of the two will act as your tiebreaker.

  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago

    I appreciate your help.

     

    But we actually want to find the top 5 by count first and then use the Sum of Total Gross as tie breaker.    If Top 5 is found 1-5, then no Sum is needed.  

     

    If we get the Top Part working above and you understand how I need Sum to be used, we could be getting close