topic Re: How to make RANKX work for columns/different categories in DAX Commands and Tips

How to make RANKX work for columns/different categories

Tomfiki — Tue, 08 Dec 2020 19:59:17 GMT

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).

I tried to use this one (CMN = Customer):

Sales_byGroup = CALCULATE([SALES+RoFo],

     FILTER(VALUES('AA_Master Data'[CMN]),

            COUNTROWS(FILTER(Customer_Groups,

                 RANKX(ALL('AA_Master Data'[CMN]),[SALES+RoFo],,DESC)>=Customer_Groups[Min_CustGroup] &&

                  RANKX(ALL('AA_Master Data'[CMN]),[SALES+RoFo],,DESC)<=Customer_Groups[Max_CustGroups])) >0))

But, as you can see from the picture, it gives me result for top 3 values per each category - red brackets - e.g. top 3 CMN values in MSGE group, then top 3 values in FCGE group etc... what I need is MSGE sales for top 3 CMN (customers) - green bracket. Then it would do the same for 3.-10, customer and so on.

I'll be gratefull for any help.

Thank you,

Tomas

I am sorry but I cannot share the .pbix file. I can make more print-screens if required.

Re: How to make RANKX work for columns/different categories

AlB — Tue, 08 Dec 2020 21:24:01 GMT

Hi Tomfiki

Sales_byGroup = CALCULATE ( [SALES+RoFo], FILTER ( VALUES ( 'AA_Master Data'[CMN] ), COUNTROWS ( FILTER ( Customer_Groups, RANKX ( ALL ( 'AA_Master Data'[CMN] ), CALCULATE ( [SALES+RoFo], 'AA_Master Data'[FieldInColumns] ), , DESC ) >= Customer_Groups[Min_CustGroup] && RANKX ( ALL ( 'AA_Master Data'[CMN] ), CALCULATE ( [SALES+RoFo], 'AA_Master Data'[FieldInColumns] ), , DESC ) <= Customer_Groups[Max_CustGroups] ) ) > 0 ) )

where 'AA_Master Data'[FieldInColumns] is, well, the field you have in the columns of your matrix visual

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

Re: How to make RANKX work for columns/different categories

Tomfiki — Wed, 09 Dec 2020 11:49:43 GMT

Hi AlB,

Thank you for the reply.

It doesn't work, it gives me this error. I am not sure if I can have 'AA_Master Data'[FieldInColumns] within CALCULATE without ALL/ALLSELECTED/VALUES etc. If I use them, then the result is incorrect, same as in original post.

Re: How to make RANKX work for columns/different categories

AlB — Wed, 09 Dec 2020 12:04:09 GMT

Tomfiki

Sorry. Looks like I forgot to write down the ALLs 🙄

Sales_byGroup = CALCULATE ( [SALES+RoFo], FILTER ( VALUES ( '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

Re: How to make RANKX work for columns/different categories

Tomfiki — Wed, 09 Dec 2020 12:34:29 GMT

AlB
I've tried your change but it still gives me the same (wrong) result as described in original post.

Re: How to make RANKX work for columns/different categories

AlB — Wed, 09 Dec 2020 12:59:02 GMT

Tomfiki

Sales_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

Re: How to make RANKX work for columns/different categories

Tomfiki — Wed, 09 Dec 2020 16:27:03 GMT

AlB
Unfortunatelly, that is still giving me the same wrong result.

Re: How to make RANKX work for columns/different categories

AlB — Wed, 09 Dec 2020 17:04:38 GMT

Tomfiki

I'd need the pbix or a mock one with dummy/anonymized data that reproduces the issue

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

Re: How to make RANKX work for columns/different categories

AntrikshSharma — Wed, 09 Dec 2020 18:39:19 GMT

Tomfiki Assuming you are trying to get top 3 based on the grand total, I have prepared a sample file for you, hopefully this gives you some ideas, since you can't share your file. The file is attached below my signature.

First table calculates top 3 based on the column total and second table calculates top 3 for the respective brand ( column )

Top 3 = VAR N = 3 VAR ColorSales = ADDCOLUMNS ( ALL ( Products[Color] ), "@Sales", CALCULATE ( [Total Sales], ALLSELECTED ( Products[Brand] ) ) ) VAR TopBrands = TOPN ( N, ColorSales, [@Sales], DESC ) VAR Result = CALCULATE ( [Total Sales], KEEPFILTERS ( TopBrands ) ) RETURN Result

Top 3 2 = SUMX ( VALUES ( Products[Brand] ), VAR N = 3 VAR Result = TOPN ( N, CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( Products, Products[Brand], Products[Color] ), "@Sales", [Total Sales] ), ALL ( Products[Color] ) ), [@Sales], DESC ) RETURN CALCULATE ( [Total Sales], KEEPFILTERS ( Result ) ) )

Re: How to make RANKX work for columns/different categories

Tomfiki — Fri, 11 Dec 2020 01:02:25 GMT

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 table

Slicer_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.

Re: How to make RANKX work for columns/different categories

AlB — Fri, 11 Dec 2020 08:39:49 GMT

Tomfiki

Great. 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.

AntrikshSharma

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

Re: How to make RANKX work for columns/different categories

AntrikshSharma — Fri, 11 Dec 2020 09:41:28 GMT

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.

Re: How to make RANKX work for columns/different categories

AlB — Fri, 11 Dec 2020 11:04:24 GMT

AntrikshSharma

Measure 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

Re: How to make RANKX work for columns/different categories

AntrikshSharma — Fri, 11 Dec 2020 11:55:45 GMT

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