01-11-2017 03:19 AM
I have the following data
Date Customer name product purchased Amount spent
1/1/2017 David A 173
1/11/2017 Steve A 164
1/20/2017 Victor N 171
1/31/2017 David S 156
2/5/2017 Mark P 148
And so on a total of 200 rows with multiple duplicate value
I want to create a funnel chart which shows the top 10 spending customers irrespective of date or product
The reslut shoul be
Customer name Amount PSent
Mark 148 and so on
Could anyone help me on this
01-12-2017 01:47 AM
In this scenario, do you want to keep duplicate rows for same customer in your result set? Or you want to have the Amount spend aggregate on customer first. Then select the TOP 10 users?
If you want to keep the duplicates for same customer, you just need to use TOPN() on this table.
Calculated Table = TOPN(10,Table,Table[Amount Spent])
If you need to aggregate your table on customer level.
Users Amount Spent= SUMMARIZE(Table,Table[Customer],"Total Amount Spent",CALCULATE(SUM(Table[Amount Spent])))
Then user TOPN on above calculated table.
Calculated Table = TOPN(10,'Users Amount Spent','User Amount Spent'[Total Amount Spent])
01-13-2017 05:55 AM
thanks for your prompt reply.
I would like the keep duplicate rows for same customer in result set. I just want to know what were the top 10 spending amount irrespective of the date and who where those customers.
I tried the TopN function as suggested by you. I am getting this error "The expression refers to multiple coulmn. Multiple coulmn cannot be converted into scalar value.