Forum Discussion

Oros's avatar
Oros
Post Prodigy
2 years ago
Solved

Top Customers

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)

 

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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 ) )

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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's avatar
      Oros
      Post Prodigy
      2 years ago

      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's avatar
        Jihwan_Kim
        Super User
        2 years ago

        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 )
        )
    )
)