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,TomasI am sorry but I cannot share the .pbix file. I can make more print-screens if required.

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

How to make RANKX work for columns/different categories

13 Replies

Tomfiki
Frequent Visitor
5 years ago
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.

AlB

Community Champion

5 years ago

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

Tomfiki
Frequent Visitor
5 years ago
AlB
Unfortunatelly, that is still giving me the same wrong result.
- AlB
  Community Champion
  5 years ago
  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

AntrikshSharma

Community Champion

5 years ago

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

Contoso Top 3.pbix2.7 MB

AlB

Community Champion

5 years ago

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

Tomfiki
Frequent Visitor
5 years ago
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.

AlB

Community Champion

5 years ago

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

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

AlB
Community Champion
5 years ago
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
- AntrikshSharma
  Community Champion
  5 years ago
  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.
AlB
Community Champion
5 years ago
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
- AntrikshSharma
  Community Champion
  5 years ago
  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