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 th...
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
Super User
Super UserYou 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.