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.

     

AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity 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 ) )
)