Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Divide Ranking Measure by the Max of the Measure

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!

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    7 years ago

    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.

     

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • SteveCampbell's avatar
        SteveCampbell
        Icon for Memorable Member rankMemorable Member
        7 years ago

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