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 ChampionMeasure Top 3
I see your point but I'm not that sure. I've just run a couple of tests and, strangely enough, I get for both your and my version readings with a higher use of the SE and, seconds later, readings with the complete opposite, a higher use of the FE. I'm dumbfounded by that; the results are not consistent 🤔. I ran the tests on the query for the visual you can get from the Performance analyzer. From what you show, for this case my version seems to be faster. Although I get similar timing performance for both versions in my tests
Measure Top 3 2
If it's the totals you're missing the measure can be modified slightly without additional complexity or impact on performance:
Top 3 2 V2B =
VAR totalSales_ = [Total Sales]
RETURN
IF (
ISFILTERED ( Products[Brand] ),
IF ( RANKX ( ALL ( Products[Color] ), [Total Sales] ) <= 3, totalSales_ ),
totalSales_
)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 Not sure how you are doing it, for me it is still the same. Also, all three measures you suggested do not add correctly.
Top 3 V2
Top 3 2 V2B:
Top 3 2 V2
