how to use SUMX

[sadath]

I have following formula to calculate total sales

TotSales'16:=if([StewTot]>=10,sum(SALESDATA[AMOUNT]),blank())

[StewTot] as the formula = Sum('STEWARD ATTENDANCE'[DAYS WORKED])

it works in PT with month on row filter, but total is getting wrong.

so, to show the correct total i tried this

SubTotal:=sumx(VALUES('STEWARD ATTENDANCE'[DAYS WORKED]),
CALCULATE (
sum(SALESDATA[AMOUNT]),
'STEWARD ATTENDANCE'[DAYS WORKED] >= 10))
returns wrong value...

 

[Matt Allington]

If the pivot table rows retun the correct value but the total doesn't, you need to simulate the rows in your formula. So you need to iterate over sumx(values(table[column in row in your pivot]),[the measure that works on rows but not in totals])

read my article here http://exceleratorbi.com.au/sum-vs-sumx-in-dax/