Forum Discussion
Top 3 Categories Based on Spending Totals
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
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.
4 Replies
johnt75Super User
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.
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.
CNENFRNLCommunity Champion
Believe me, the intricacy is way beyond your comprehension.
This is the underlying dataset with ranks within respective category.