I've spend hours pouring over documentation on SUMMARIZE, SUMMARIZECOLUMNS, ADDCOLUMNS AND TOPN, and I just cannot get this simple calculation to come out correctly. I've even looked at results with DAX Studio, and it's always wrong. I'm trying to do this the "right way," i.e., without using deprecated techniques like using SUMMARIZE to add columns. The problem is simple and classic: summarize a sales table by Salesman, and add up the sales for the top N. Here's some data. I presume I need to calculate an intermediate summary table -- but maybe that's not necessary?
If someone could just layout the DAX code, I'd greatly appreciate it! I've tried everything!
Here's my code:
Top N Acc Rev.. =
var N = MAX( 'Numbers'[RankNum] )
VAR CaseTable = SaleData
VAR Table0 = SUMMARIZE( CaseTable, [Salesman], [Sale])
VAR Table1 = TOPN(N, Table0, [Salesman], DESC)
RETURN
SUMX( Table1, [Sale] )
The data wasn't as complex as it should be. I also had a mistake of ranking by Salesman versus ranking by Salesman's total sales in the TOPN function. So here is my new data and code which finally works. The results for N = 1 or 2 are 625 and 1127. Thanks, Jos Wooley!
SaleTest =
VAR N = MAX(Numbers[RankNum])
VAR CaseTable = Filter(SalesData, [Sale] > 100 )
VAR Table0 = SUMMARIZE(CaseTable, [Salesman], "Sales", SUM( SalesData[Sale] ) )
VAR Table1 = TOPN(N, Table0, [Sales], DESC)
RETURN SUMX( Table1, [Sales] )