Forum Discussion

Oros's avatar
Oros
Icon for Post Prodigy rankPost 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 ) )
Oros's avatar
Oros
Icon for Post Prodigy rankPost 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
Icon for Super User rankSuper 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
    Icon for Post Prodigy rankPost 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
      Icon for Super User rankSuper 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 ) )
      • Oros's avatar
        Oros
        Icon for Post Prodigy rankPost Prodigy
        2 years ago

        Hi Jihwan_Kim ,

         

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