question on 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=7DF5E31C189063EC%21124&cid=7DF5E31C189063EC

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.

 

[DRSteele]

I think I understand what you're after. I believe you wish to perform ranking with conditions, or RANKIF (if such a function existed). Watch this video on youtube from ExcelIsFun and let us know if it helps. https://www.youtube.com/watch?v=rCeTG2k2T6I

 

[XLBob]

I think I understand what you're after. I believe you wish to perform ranking with conditions, or RANKIF (if such a function existed). Watch this video on youtube from ExcelIsFun and let us know if it helps. https://www.youtube.com/watch?v=rCeTG2k2T6I

I need a DAX solution, thanks anyway.

 

[DRSteele]

Sorry; I didn't realise that RANKX was a DAX function. Best of luck.

 

[XLBob]

Sorry; I didn't realise that RANKX was a DAX function. Best of luck.

I have managed to come up with a solution using Calculate and Allexcept. What I am trying to achieve in the end is to have 5 pie charts for the top 5 category, then for each pie chart, it has 6 pieces for the top 5 subcategory and "other" which is the group of other subcategory. Because at the moment, Power BI desktop does not have support "Top X" filter which excel does, so I have to use calculated column to find the ranking and add a new subcategory column. I hope someone can have a better or more elegant solution. I will come back to share if I have one, just need more time to think about this.