RANKX

[XLBob]

I have sales data at monthly level and you can find it on my onedrive. The file name is "sample" and below is the link.
https://onedrive.live.com/?id=7DF5E31C1 ... 1C189063EC

How do I add a calculated column to rank each category for the same month and channel? For example, rank all category for the month of Nov 2015 in channel CH4.
Then for each category I want to rank the subcategory and keep the top 5 as it is and combine the rest into a new subcategory called "other". For example, in Nov 2015 and channel CH4, category with ranking 2 has x (x>10)subcategories, rank these subcategories and for the subcategory with ranking from 6 to x combine them into a new subcategory called "other'.

Due to the merge as mentioned above I guess it has to be done with calculated columns instead of measures

Thanks for your help.

[kazlik · Accepted solution]

There are some examples in this post that might help you,

http://www.powerpivotpro.com/2015/06/rankx-apalooza-within-and-across-groups-with-filters-etc/