Top 3 Categories Based on Spending Totals

[JKPBI]

Hello:

I am looking to create 3 calculated columns returning the top 1st, 2nd, and 3rd categories which a customer has spent the highest amounts on (over multiple transactions).

Below find the kind of data I'm working with, and the result I'm looking for.

Category Spending
Person	Date	Category	Amount
Bill	1/1/2022	A	$                             75.00
Bill	1/2/2022	B	$                          100.00
Bill	1/3/2022	C	$                             50.00
Bill	1/4/2022	A	$                          100.00
Bill	1/5/2022	C	$                          100.00
Sue	1/6/2022	B	$                          100.00
Sue	1/7/2022	A	$                             50.00
Sue	1/8/2022	B	$                             50.00
Sue	1/9/2022	C	$                          125.00
Sue	1/10/2022	B	$                             50.00
Greg	1/11/2022	A	$                          100.00
Greg	1/12/2022	C	$                          200.00
Greg	1/13/2022	C	$                             25.00
Greg	1/14/2022	B	$                          100.00
Greg	1/15/2022	A	$                             50.00

Customer Lookup
Person	1st Highest	2nd Highest Category	3rd Highest Category
Bill	A	C	B
Sue	B	C	A
Greg	C	A	B

thank you so much for any assistance

[CNENFRNL]

Believe me, the intricacy is way beyond your comprehension.

This is the underlying dataset with ranks within respective category.

[johnt75 · Accepted solution]

You can create measures like

Top product = 
var summaryTable = ADDCOLUMNS( SUMMARIZE('Table','Table'[Category]), "@amt", CALCULATE( SUM('Table'[Amount])) )
return CONCATENATEX( FILTER( summaryTable, RANKX(summaryTable, [@amt]) = 1 ), [Category], ", ")

and just change the 1 to 2 or 3 for the other measures.

In the event of a tie this will return all the products which are at that rank separated by a comma.

[JKPBI]

@johnt75 Wow this is so compact, thank you!

a follow-up (isn't there always?) what would I plug into this formula to also get a corrosponding measure to sum up all their spending on the ranked category (3 more)?

Thank you again! 

[johnt75]

You could get the amount with

Spend on Top product = 
var summaryTable = ADDCOLUMNS( SUMMARIZE('Table','Table'[Category]), "@amt", CALCULATE( SUM('Table'[Amount])) )
return MAXX( FILTER( summaryTable, RANKX(summaryTable, [@amt]) = 1 ), [@amt])

In case of ties that will give the amount spent per category, not the total spent.