Forum Discussion
Solved
How to make RANKX work for columns/different categories
Hello, Could you please help me get this DAX formula working? I need a formula which will always display result for Customer Group by total sales for that customer (e.g. for top 3 customers). ...
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.
AlB
Community Champion
Community ChampionSales_byGroup =
CALCULATE (
[SALES+RoFo],
FILTER (
ALL ( 'AA_Master Data'[CMN] ),
COUNTROWS (
FILTER (
Customer_Groups,
RANKX (
ALL ( 'AA_Master Data'[CMN] ),
CALCULATE ( [SALES+RoFo], ALL ( 'AA_Master Data'[FieldInColumns] ) ),
,
DESC
) >= Customer_Groups[Min_CustGroup]
&& RANKX (
ALL ( 'AA_Master Data'[CMN] ),
CALCULATE ( [SALES+RoFo], ALL ( 'AA_Master Data'[FieldInColumns] ) ),
,
DESC
) <= Customer_Groups[Max_CustGroups]
)
) > 0
)
)
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