Top Customers

[Oros]

Hello,

What would be the correct measure to show the Top 10 customer over a period of time?  Thanks. 

The resulting ranking is not based on the grand total but based on the total sales for a given month (or column period). 

In the example below, if there are 500 customers, the measure will show only 16 customers that fall into the TOP 10 customers for the given period which is January to May (on a month-to-month basis)

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

In my sample, I tried to achieve top 3, and I hope you can reauthor the measure in your sample.

Please check the below picture and the attached pbix file.

Sales: = 
SUM( sales[sales])

WINDOW function (DAX) - DAX | Microsoft Learn

Sales top 3: = 
VAR _selectedperiod =
    ALLSELECTED ( 'calendar'[MOnth-Year sort] )
VAR _t =
    ADDCOLUMNS (
        CALCULATETABLE (
            SUMMARIZE (
                ALL ( sales ),
                customer[customer],
                'calendar'[MOnth-Year sort],
                'calendar'[Month-Year]
            ),
            'calendar'[MOnth-Year sort] IN _selectedperiod
        ),
        "@sales", [Sales:]
    )
VAR _group =
    GROUPBY (
        _t,
        customer[customer],
        "@allsales", SUMX ( CURRENTGROUP (), [@sales] )
    )
VAR _toplist =
    SUMMARIZE (
        WINDOW ( 1, ABS, 3, ABS, _group, ORDERBY ( [@allsales], DESC ) ),
        customer[customer]
    )
RETURN
    CALCULATE (
        SUM ( sales[sales] ),
        KEEPFILTERS ( customer[customer] IN _toplist )
    )

[Oros]

Hi @Jihwan_Kim,

Thank you very much for your quick reply and for taking the time to send a pbix file.  I think that you are very close to the intended result.

One thing that I noticed is that the 2 tables do not show the top 3 per month.

(we do NOT need the top 3 by grand total)

 (we need to show all the top 3 customers in a given month.  we do NOT need show the customers who have never been in the top 3 in a given month.  the ones highlighted in red have never been in the top 3 in any of the given months). 

All customers in yellow have been in the top 3 in a given month.  Thanks again.

[Jihwan_Kim]

Hi,

Thank you for your message, and please check the below picture and the attached pbix file whether it suits your requirement.

Sales top 3 v2: =
CALCULATE (
    SUM ( sales[sales] ),
    KEEPFILTERS (
        WINDOW (
            1,
            ABS,
            3,
            ABS,
            CALCULATETABLE (
                SUMMARIZE (
                    sales,
                    customer[customer],
                    'calendar'[MOnth-Year sort],
                    'calendar'[Month-Year]
                ),
                REMOVEFILTERS ( customer[customer] )
            ),
            ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), DESC )
        )
    )
)

[Oros]

Hi @Jihwan_Kim,

I think that you actually nailed it! How do I show the rest of the numbers like this? I would  like to show the non-highlighted numbers, together with the yellow highlighted numbers.  Thanks again.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Sales top 3 v3: =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( sales ),
            customer[customer],
            'calendar'[MOnth-Year sort],
            'calendar'[Month-Year]
        ),
        "@result",
            CALCULATE (
                SUM ( sales[sales] ),
                KEEPFILTERS (
                    WINDOW (
                        1,
                        ABS,
                        3,
                        ABS,
                        CALCULATETABLE (
                            SUMMARIZE (
                                sales,
                                customer[customer],
                                'calendar'[MOnth-Year sort],
                                'calendar'[Month-Year]
                            ),
                            REMOVEFILTERS ( customer[customer] )
                        ),
                        ORDERBY ( CALCULATE ( SUM ( sales[sales] ) ), DESC )
                    )
                )
            )
    )
VAR _list =
    SUMMARIZE ( FILTER ( _t, [@result] <> BLANK () ), customer[customer] )
RETURN
    CALCULATE ( SUM ( sales[sales] ), KEEPFILTERS ( customer[customer] IN _list ) )

[Oros]

Hi @Jihwan_Kim ,

You are an absolute Superhero!!!  It works!  Thanks!