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

Having 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

hnguy71's avatar
hnguy71
Super User
3 years ago

bdehning it would just be replaced by your sum of total gross incurred. This would be then your tiebreaker value.

 

[Count of Total Gross Incurred] + [Sum of Total Gross Incurred],

 

  • 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?

    • bdehning's avatar
      bdehning
      Post Prodigy
      3 years ago
      Second Return says Unexprected Return

          RETURN _tmpRank
      ,
      RETURN
      • bdehning's avatar
        bdehning
        Post Prodigy
        3 years ago

        Almost there but I have 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] + [Sum of Total Gross Incurred],
                ,
                DESC,
                DENSE
            )
        )
         
        It is not getting past the 2nd return as it says Unexpected Return?
  • 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.