Forum Discussion

sandy2405's avatar
sandy2405
Frequent Visitor
7 years ago
Solved

Ranking on Multiple Measures

Hi All,

 

I am facing issue with calculating rank on mutlple measures. In my case, I have to assign rank to players based on fifties, they have scored.
In my data set, I have tie between Warner and Gambhir (like below screenshot). I am calculating the rank on below formula:

Rank By Most Fifties = RANKX(ALL(Deliveries[batsman]), [Batsman Total Fifties],,DESC,Skip)

 

 

 

 

 

 

 

 

 

 

To deal with tie, I need to re-assign rank based on below three measure:

  1. Total Fifties
  2. Total Runs
  3. Average

I am unable to formulate the DAX for calculating the rank based on these 3 measures. Please provide your input(s) here.

 

Thanks,

Sandy

Tips and Tricks
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi sandy2405

     

    You may create a measure like below.For further, you may refer to this post.

    Measure  = 
RANKX (
    ALL ( 'Table' ),
    RANKX ( ALL ( 'Table' ), [Measure1],, ASC )
        + DIVIDE (
            RANKX ( ALL ( 'Table' ), [Measure2],, ASC ),
            COUNTROWS ( ALL ( 'Table' ) ) + 1
        )
        + DIVIDE (
            DIVIDE (
                RANKX ( ALL ( 'Table' ), [Measure3],, ASC ),
                ( COUNTROWS ( ALL ( 'Table' ) ) + 1 )
            ),
            COUNTROWS ( ALL ( 'Table' ) ) + 1
        ),
    ,
    ASC,
    DENSE
)

    Regards,

    Cherie

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi sandy2405

     

    You may create a measure like below.For further, you may refer to this post.

    Measure  = 
RANKX (
    ALL ( 'Table' ),
    RANKX ( ALL ( 'Table' ), [Measure1],, ASC )
        + DIVIDE (
            RANKX ( ALL ( 'Table' ), [Measure2],, ASC ),
            COUNTROWS ( ALL ( 'Table' ) ) + 1
        )
        + DIVIDE (
            DIVIDE (
                RANKX ( ALL ( 'Table' ), [Measure3],, ASC ),
                ( COUNTROWS ( ALL ( 'Table' ) ) + 1 )
            ),
            COUNTROWS ( ALL ( 'Table' ) ) + 1
        ),
    ,
    ASC,
    DENSE
)

    Regards,

    Cherie