Agents sales filtered by top 2 Customers

DAX DAX Calculations
1

Hi,

Would like to seek for your help what is the problem of my DAX.
image

Top N Customers by Sales Value CM = 
                VAR myNNum = SELECTEDVALUE ( NNumber20[NNumber] )
                Var myTopBottom = SELECTEDVALUE ( DimTopBottom[TopBottom] )
                VAR myPeriod =
                    SELECTEDVALUE ( DimDatesAttributes[NAttribute] )
                VAR myTopSalesMth =
                     CALCULATE (
                        [Sales CM],
                        FILTER (
                            VALUES ( DimAR_CUSTOMER[COMPANYNAME] ),
                            IF (
                                ISBLANK ( [Sales CM] ),
                                BLANK (),
                                RANKX (
                                    FILTER ( ALL ( DimAR_CUSTOMER[COMPANYNAME] ), NOT ( ISBLANK ( [Sales CM] ) ) ),
                                    [Sales CM],
                                    ,
                                    DESC
                                ) <= myNNum
                            )
                        )
                    )
                VAR myBottomSalesMth =
                     CALCULATE (
                        [Sales CM],
                        FILTER (
                            VALUES ( DimAR_CUSTOMER[COMPANYNAME] ),
                            IF (
                                ISBLANK ( [Sales CM] ),
                                BLANK (),
                                RANKX (
                                    FILTER ( ALL ( DimAR_CUSTOMER[COMPANYNAME] ), NOT ( ISBLANK ( [Sales CM] ) ) ),
                                    [Sales CM],
                                    ,
                                    ASC
                                ) <= myNNum
                            )
                        )
                    )

RETURN
    SWITCH ( myTopBottom, "Top", myTopSalesMth, "Bottom", myBottomSalesMth )

However, when i include Agent Name, it become as follows:

image
image756×181 15.2 KB

Any idea what’s wrong with my DAX to generate the Agent Sales by Top N Customers?

Your advise was highly appreciated.

Thank you.

Dennis Tiong

2

Hi @dennistgc,

It’s quite difficult to understand without looking at data model etc and I would appreciate to share pbix file in case below change does not work. I can see you are removing filters from DimAR_CUSTOMER[COMPANYNAME] column, however, you are adding Agent Name as well without removing filters. So, your best bet could be to either remove filter from entire DimAR_CUSTOMER table or from Agent Name as well in both rank functions.

FILTER ( ALL ( DimAR_CUSTOMER[COMPANYNAME] )

Change Above to either these 2:

FILTER ( ALL ( DimAR_CUSTOMER ) 

OR

FILTER ( ALL ( DimAR_CUSTOMER[COMPANYNAME] ,DimAR_CUSTOMER[DESCRIPTION])

Please let me know if it works or not?

3

Thanks for posting your question @dennistgc. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

4

Hi @dennistgc, we’ve noticed that no response has been received from you since the 11th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!