Ranking 2 Measures and Filtering

[mjholland]

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 amend based on a number of filters and slicers I apply to the report.

My data has a large number of data points for each store, relating to question responses. For each store I'm able to work out a % for these responses by quarter. I'm looking to identify the top stores by ranking those with the highest % reponse with the largest number of data points (count of data field). The result would be to show to the top and bottom stores.

I would need the ranking to be affected by a number of filters.

Any suggestions as to how I could do this?

Thanks,

MJHolland

[Sean]

@mjholland Check out the solution here...

http://community.powerbi.com/t5/Desktop/How-to-Rank-a-list-based-on-2-values-double-rankX/m-p/44076#...

[mjholland]

@Sean this isn't working for me. I've given a screenshot below:

I've created a really simple table with the list of stores, their % and count of data points (count of entry responses). When I add in the DAX solution you gve me it ranks these stores as 1, when it should really have a different value for each store.

Any thoughts?

[Sean]

@mjholland I know this pattern works - so we should probably look at the formulas you are using for

the 2 MEASURES => Entry % and Count of Entries - can you post the formulas?

[mjholland]

@Sean sure here you go:

Entry % = sum('All Mults Data'[Entry Response])/counta('All Mults Data'[Entry Response] )

Count of Entry = count('All Mults Data'[Entry Response])

Initially I didn't have Count of Entry as a measure, I just has COUNT('All Mults Data'[Entry Response]) in where the second measure would appear, but this didn't work either.

[Sean · Accepted solution]

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

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

[mahimabedi]

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.