Tsudoi has attended:
Excel PowerPivot course
Excel VBA Introduction course
Dax calculation to show power pivot table correctly
I wan to do volume effect & price effect variance calculation to last year in Dax and show in power pivot.
So when the Rows in pivot table changes, a total at the level is correctly shown by calculating variance to each of fields vertically.
I can calcite year on year variance for each row but when it comes to calculating a raw against a total to see the variance from the total, I don’t know how to do it in Dax.
I hope my question is clear enough here if not, please let me know. I’d like to explain in more detail.
RE: Dax calculation to show power pivot table correctly
Hi Tsudoi,
If I understand your question right, you can use the measure below. If you have a calculated column where you have calculated the row variance and the column header is [Variance]:
% variance:=divide(sum([var]),calculate(sum("your table name"[var]),all("your table name)),0)
Please let me know if it is not what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Dax calculation to show power pivot table correctly
Hi Jens, just wanted to drop you a line to say thanks for that. I have been struggling to make sense out of calcs using your formula but not working. I think I need to revisit and figure out in a different way, so I might come back later.
Thanks anyway.
