Rank Measure

[bdehning]

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?

[bdehning]

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.   

[bdehning]

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.  

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[bdehning]

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

[Ashish_Mathur]

Hi,

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

[bdehning]

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]

@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]

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.   

[hnguy71]

@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 1

2 + 2/100 = Rank 2

3 + 3/100 = Rank 3

4 + 4/100 = Rank 4

5 + 5/100 = Rank 5

5 + 6/100 = Rank 6

5 + 7/100 = Rank 7

5 + 8/100 = Rank 8

5 + 9/100 = Rank 9

[bdehning]

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]

@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]

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

[hnguy71]

@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]

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]

@bdehning that's correct.

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

[bdehning]

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

[bdehning]

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

[bdehning]

Second Return says Unexprected Return

    RETURN _tmpRank

,

RETURN

[bdehning]

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]

 I want 

1

2

3

4

5

6

7

8

9

10

11