Forum Discussion
How to make RANKX work for columns/different categories
AntrikshSharma AlB Thank you both very much!
I tried your Top 3 formula but it was still showing me wrong result when "Color" was not in the visual.
I managed to get it working by adding a new tableSlicer_CustGroups = DISTINCT(VALUES('AA_Master Data'[CMN]))and a new ranking column.
Customer Grp =
VAR topx = RANKX(ALL(Slicer_CustGroups[CMN]),CALCULATE(SUM('AA_Master Data'[Sales+RoFoACT]),ALL('AA_Master Data'[CMN])),,DESC)
RETURN
SWITCH(TRUE(),topx<=3,"1) Top 3 Customers",topx<=10,"2) 4.-10. Cust.",topx<=20,"3) 11.-20. Cust.",topx<=50,"4) 21.-50. Cust.",topx<=100,"5) 51.-100. Cust.","6) Remaining Cust.")I know it is not the cleanest solution but it works which is the main thing to me and I can also use it as a slicer.
Community ChampionGreat. I'm still curious as to why the previous solution didn't work. Essentially you're doing the same withing the RANKX. Probably something that is not showing in the examples provided.
I believe you can get the exact same results with simpler measures:
Top 3 V2 =
IF( RANKX(ALL(Products[Color]), CALCULATE([Total Sales], ALL(Products[Brand]))) <=3, [Total Sales])Top 3 2 V2 =
IF( RANKX(ALL(Products[Color]), [Total Sales]) <=3, [Total Sales])
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- AntrikshSharma
AlB Measure Top 3 is optimized, most of the work is done in the Storage engine and only 3 queries are executed.
Quries generated by the measure you have suggested:
Measure 2 - agreed is complex in the looks, but I was trying to get the column total as well.
