Divide Ranking Measure by the Max of the Measure

[Anonymous]

I have a list of clients and an associated "tech rating". I have a "ranking measure" (below) to rank the clients by their tech rating.

Ranking Measure = 
MINX(
    FILTER(
        SELECTCOLUMNS(
            ALLSELECTED(Table1),
            "Index", Table1[School Systems.Campusname],
            "Rank", RANKX(ALLSELECTED(Table1),Table1[Tech Rating],,DESC,Dense)
        ),
        [Index] = MAX(Table1[School Systems.Campusname])
    ),
    [Rank]
)

This results in the table below.

What I now need is to convert the "Ranking Measure" to a percentage range. In Excel it would be simply: Ranking Measure/Max(Ranking Measure). I have seen this done before, but I'm just having trouble applying it to my situation.

Thanks!

[SteveCampbell]

Could you do:

Ranking % = 
Divide( [Ranking Measure] , COUNTROWS(VALUES (Table1[School Systems.Campusname])) )

Appreciate your Kudos
I love to share - connect with me!

Stay up to date on  
Read my blogs on  

[Anonymous]

This does not seem to work. I think it's because the client name is not a number?

[SteveCampbell]

Sorry, missed a function - I edited the code - try now

[Anonymous]

This was actually my first idea, but this assums that there are no duplicate "Ranking Measures". There are a few that are duplicate and I don't want the measure to double count. Also, the result is just the mirror of the "Ranking Measure". I think this is because the measure is just dividing the "Ranking Measure" by the count of clients in the row, which is always just 1...

[SteveCampbell]

Sorry, Try:

Ranking % = 
var _rnk = 
MINX(
    FILTER(
        SELECTCOLUMNS(
            ALLSELECTED(Table1),
            "Index", Table1[School Systems.Campusname],
            "Rank", RANKX(ALLSELECTED(Table1),Table1[Tech Rating],,DESC,skip)
        ),
        [Index] = MAX(Table1[School Systems.Campusname])
    ),
    [Rank]
)
return
Divide( _rnk , CALCULATE(COUNTROWS(VALUES (Table1[School Systems.Campusname])),ALLSELECTED(Table1)))

I would change from DENSE to SKIP so the values total to 100, or you can change back depending on your requirements. What would be the desired output if the rank is the same? This would show the same %, which is what the excel solution you described would do

Appreciate your Kudos
I love to share - connect with me!

Stay up to date on  
Read my blogs on  

[Anonymous]

At first glance, this seems correct, but I noticed that if I create a slicer and filter out clients, the bottom percentage is no longer 0... is there a way to make it so the distribution is always from 0% to 100%?

[v-lid-msft · Accepted solution]

Hi  @Anonymous ,

We can just add a little change to @SteveCampbell 's formula.

Ranking % = 
VAR _rnk =
    MINX (
        FILTER (
            SELECTCOLUMNS (
                ALLSELECTED ( 'Table1' ),
                "Index", 'Table1'[name],
                "Rank", RANKX ( ALLSELECTED ( 'Table1' ), 'Table1'[rate],, DESC, SKIP )
            ),
            [Index] = MAX ( 'Table1'[name] )
        ),
        [Rank]
    )
VAR _all =
    CALCULATE ( COUNTROWS ( VALUES ( 'Table1'[name] ) ), ALLSELECTED ( 'Table1' ) )
RETURN
    IF ( _all = 1, 1, DIVIDE ( _rnk - 1, _all - 1) )

Here is another formula to achieve the same goal.

Ranking % 2 = 
IF (
    COUNTROWS ( ALLSELECTED ( 'Table1' ) ) = 1,
    1,
    DIVIDE (
        RANKX (
            ALLSELECTED ( Table1[name] ),
            CALCULATE ( SUM ( 'Table1'[rate] ) ),
            ,
            DESC,
            SKIP
        ) - 1,
        COUNTROWS ( ALLSELECTED ( 'Table1' ) ) - 1
    )
)

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

There we go! Awesome! Thank you so much!