dax calculation show power

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Dax calculation to show power pivot table correctly

Dax calculation to show power pivot table correctly

resolvedResolved · High Priority · Version 365

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.


 

Excel tip:

Finding your worksheets quickly

The arrows to the left of the worksheets are used to move between one sheet at a time or first / last worksheet, but if you right click on the arrow buttons it gives you all worksheets in your workbook.

View all Excel hints and tips


Server loaded in 0.12 secs.