Displaying the largest number of sales by a customer based on user chosen date range

[Anonymous]

I have a report where the user chooses the date period and the cards update to show the number of purchases and individual customers.  I've also created a card that show the average number of purchases per customer, by using one measure divided by the other measure. 

I'd also like to display the largest number of purchases made by one customer, but unsure how to approach this.  I tried summarise, but this creates a new table and the date slicer has no effect on the table.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try measure like this:

Measure = 
CALCULATE (
    MAXX (
        SUMMARIZE (
            'Table',
            'Table'[customer],
            "Total", SUM ( 'Table'[Purchase] )
        ),
        [sum_]
    ),
   ALLSELECTED('Table'[date]
))

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@V-lianl-msft  If i wanted to display a column chart, based on this dataset, the x-axis would be the number of purchases made eg in your dataset 15, 33, 37, y-axis would be the number of customers making those number of purchase eg, your dataset would be, 1,1,1.

How can I make that table above that would change with the dates?  I suspect it's something to with the same code with SUMMARIZE, but without the MAXX function and create a table?  Something like below, but correct?!

Table = CALCULATE(

        SUMMARIZE(

            Missing,

            Missing[LL_ID],

            "Total",SUM(Missing[Missing ID])

            ),

        ALLSELECTED(Dates[Date])

)

[Anonymous]

@V-lianl-msft  Wow, very clever indeed.  Works a treat 😎

[amitchandak]

@Anonymous , Create a Rank on the customers on no of purchase and you can filter on Rank =1 for top customer

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415