Forum Discussion

Tomfiki's avatar
Tomfiki
Frequent Visitor
5 years ago
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). ...
DAX Commands
RANKX
  • Tomfiki's avatar
    Tomfiki
    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's avatar
AlB
Icon for Community Champion rankCommunity 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's avatar
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.