98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum 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
Resolved · 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.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Finding your worksheets quicklyThe 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. |