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

      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's avatar
        Jihwan_Kim
        Super User
        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 ) )