Forum Discussion

mjholland's avatar
mjholland
Advocate II
10 years ago
Solved

Ranking 2 Measures and Filtering

Hi,   I'm looking to rank a list of stores based on 2 calculations - one a measure working out a percentage, the other would be the count of a field. I then want to be able to have this ranking ame...
  • Sean's avatar
    Sean
    10 years ago

    mjholland on the 3rd, 4th and 6th lines ONLY add the column name

     

    so it will look like => ALL ( 'All Mults Data'[Branch Name] )

     

    Also change to counta the 2nd measure or the 1st to count depending on your data type

Sean's avatar
Sean
Community Champion
10 years ago

mjholland on the 3rd, 4th and 6th lines ONLY add the column name

 

so it will look like => ALL ( 'All Mults Data'[Branch Name] )

 

Also change to counta the 2nd measure or the 1st to count depending on your data type

mjholland's avatar
mjholland
Advocate II
10 years ago

You're a hero, it's worked!

 

Here's what I've ended up with:

 

Final Rank =
RANKX (
    ALL ( 'All Mults Data'[Branch Name] ),
    RANKX ( ALL ( 'All Mults Data'[Branch Name] ), [Entry %],, ASC )
        + DIVIDE (
            RANKX ( ALL ( 'All Mults Data'[Branch Name] ), [Count of Data Points],, ASC ),
            ( COUNTROWS ( ALL ( 'All Mults Data' ) ) + 1 )
        )
)

 

For [Count of Data Points] I've used COUNTA rather than COUNT, which has given me the result I'm looking for.

 

It works with all the filtering too. Thanks for all your help.

 

MJHolland

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    How can i rank as per a dynamic measure, not a fixed column.

     

    I want to create a rank measure, using 2 measures, not 2 columns. Rankx seems to work only for columns in tables.