I'm trying to rank a group of records by their percentile performance within a separate subcategory. For example, in the Superstore dataset, I would be performing the following calculations:
1. For every purchase, calculate the purchases' profit as a percentile compared to other purchases in that category (I did this)
2. For every customer name, calculate the average of the percentiles across all purchases by that customer, and rank descending by this value (I did this)
3. Rank customers by average percentile (so in the sample workbook Mitch Willingham would be 1, Bryan Spruell 2, and so on.) I'm able to get these ranks to work somewhat, but the ranks always either increment by number of purchases or (seemingly arbitrarily) split purchases within a single customer name and assign them different ranks.
If anyone can provide any tips on if/how this is possible then that would be awesome! I realize that this analysis does not make much sense in the context of the Superstore workbook but the application I'm working on warrants it. Thanks a lot!
You're better using RANK_DENSE()
for your [Performance Rank] calculation.
Hope it helps.