This is the first time I've had to use dax formulas and I've only been playing around with them for a day so am a complete novice so please bear that in mind....
I'm trying to do volume, rate, mix calculation in power pivot rather than just straight excel (Excel 2013).
I have a calculated field in a pivot table
Mix=iferror((sum([Sales])-((sum([Sales Forecast])*(1+calculate([Sales vs Versus %],ALLSELECTED(Range))))))*([Profit % Forecast]-calculate([Profit % Forecast],ALLSELECTED(Range))),0)
This is calculating beautifully except on the sub totals. For these I need it to sum the output, rather than calculating the formula itself.
The data is summarised at various different levels and has filters applied but simplified below is what I'd expect.
DAX.png
We've tried numerous ways. One that was suggested
Mix2=SUMX(VALUES(Range[Product Level 2]), [Mix])
is doing something.......but no idea what as it doesn't balance to either number.
Any help you can give would be greatly appreciated...
Bookmarks