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.
CNENFRNL
Community Champion
Community ChampionBelieve me, the intricacy is way beyond your comprehension.
This is the underlying dataset with ranks within respective category.