Update with Sales Adjusted

[Anonymous]

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

[Anonymous]

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.

[MVenables]

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