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