Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Update with Sales Adjusted

I have below measure to rank customers by sales:

 

Sales Amt =
Var SalesOfAll=
    CALCULATE([Total Sales],REMOVEFILTERS(Groupcustomer[Groupcustomer]))
Return
    IF(
        ISINSCOPE(Groupcustomer[Groupcustomer]),
        VAR ProductsToRank = 'TopN'[Value for TopN]
        VAR IsOtherSelected = SELECTEDVALUE(Groupcustomer[Groupcustomer])="Others"
        VAR ProductsWithSalesAmt =
            ADDCOLUMNS(
                ALLSELECTED(Groupcustomer[Groupcustomer]),
                "@Amt", [Total Sales]
            )
        VAR Top3Prods = TopN (ProductsToRank,ProductsWithSalesAmt,[@Amt])
        VAR SalesOfTop3 = SUMX(Top3Prods,[@Amt])
        VAR Result = IF(IsOtherSelected,SalesOfAll-SalesOfTop3,[Total Sales])
        Return Result,
        SalesOfAll
    )
 
Where Groupcustomer is: 
Groupcustomer=
UNION(
    ALLNOBLANKROW('Sales Autoupdate'[Groupcustomer]),
    {"Others"}
)
 
I have added a source called sales adj. where I do some adjustment between dates in a manual excelsheet. I have added to sales into "Total Sales" but dont get the ranking correctly. I think I need to add the new source to Groupcustomer like below but doesnt get it to work
 
Groupcustomer=
UNION(
    ALLNOBLANKROW('Sales Autoupdate'[Groupcustomer] + 'Sales Adj'[Groupcustomer] ),
    {"Others"}
)
 
All support appreciated
 
Need Help

2 Replies

  • MVenables's avatar
    MVenables
    Icon for Advocate II rankAdvocate II
    2 years ago

    Hi,

     

    Just to get an idea of where you are going with this. Could you not just add your Sales measure to the filter pane and advance filter by Top N? As per my example below. Seems quite complex for what you might be trying to achieve. Correct me if im wrong

    Thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    I think you can use the rankx function for comprehensive sorting, if you can, share your sample data to better solve the problem for you.RANKX function (DAX) - DAX | Microsoft Learn

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.