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]),
            ALLSELECTED ( 'InjuryCause'[Cause Grouping] )
        ),
        [Count of Total Gross Incurred]
    )
)
 
How do I rewrite it to be able to add DESC and Dense?
 
Need Help

26 Replies

  • hnguy71's avatar
    hnguy71
    Super User
    3 years ago

    Hi bdehning ,

     

    there should be optional parameters after your count measure. Something like this:

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

      I get 

      "Unexpected value for ORDER argument in RANKX function. Use 0/FALSE/DESC for descending order or 1/TRUE/ASC for ascending order." using that.  

      • hnguy71's avatar
        hnguy71
        Super User
        3 years ago

        bdehning ,

         

        can you try this?

            IF (
    ISINSCOPE( 'InjuryCause'[Cause Grouping]),
    RANKX (
        CALCULATETABLE (
            VALUES ( 'InjuryCause'[Cause Grouping]),
            ALLSELECTED ( 'InjuryCause'[Cause Grouping] )
        ),
        [Count of Total Gross Incurred],
        ,
        DESC,
        DENSE
    )
)

         

        maybe I was missing an extra comma, added it after your count.

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

    Ok it took that but I need to add DENSE as I get muliple same ranking numbers?

  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago

     Or add someting to eliminate this   

    1

    2

    3

    4

    5

    5

    5

    5

    5   

  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago
    The following although not showing error still produces this
     
     IF (
        ISINSCOPE( 'InjuryCause'[Cause Grouping]),
        RANKX (
            CALCULATETABLE (
                VALUES ( 'InjuryCause'[Cause Grouping]),
                ALLSELECTED ( 'InjuryCause'[Cause Grouping] )
            ),
            [Count of Total Gross Incurred],
            ,
            DESC,
            DENSE
        )
    )
     
    1
    2
    3
    4
    5
    5
    5
    5
    5
    • 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.   

    • bdehning's avatar
      bdehning
      Post Prodigy
      3 years ago

      I wish I could,  but data needs to be protected.  

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

        Anonymise some data clearly showing the issue that you are facing and share the download link of that PBI file.

  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago

    As I use this 

    IF (
        ISINSCOPE( 'InjuryCause'[Cause Grouping]),
        RANKX (
            CALCULATETABLE (
                VALUES ( 'InjuryCause'[Cause Grouping]),
                ALLSELECTED ( 'InjuryCause'[Cause Grouping] )
            ),
            [Count of Total Gross Incurred],
            ,
            DESC,
            DENSE
        )
    )
    and get ties. 
     
    Now, I  just want to use the [Sum of Total Gross Incurred] of each Ranked 'InjuryCause'[Cause Grouping] to break the ties and need help to add that to the measure.  
  • bdehning's avatar
    bdehning
    Post Prodigy
    3 years ago

    I have reworked the measure and now I have this

     

    Rank Cause Grouping Count =
    RANKX(
       
            ALLSELECTED(InjuryCause[Cause Grouping]),
            CALCULATE(
               COUNT(LossRunToExcel[Total Gross Incurred])),
            ,
            DESC, Dense
          )
     
    Now I just need to come up with the rest to break ties and I want to use SUM(LossRunToExcel[Total Gross Incurred] to break the ties.