Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
@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?
@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?
@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.
@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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |