Waterfall - Profit Variance from (normalised?) table

[bswylie]

Please forgive my ignorance if this is a basic question, but I've come short and I'm hoping someone can point me in the correct direction with DAX in Power BI.

Considering a very simple relationship, Profit = Revenue - Costs, and seeing the image below at the bottom of this post:

I'm trying to create a waterfall chart which shows waterfall variance between a profit budget and a profit actual, with the components of the variances being the variances on the items that form the basic Profit Equation. viz - Revenue Variance and Cost Variance between budget and actuals. The source TABLE is what I believe is considered a normalised table (i.e. not having a column  for each metric - it is non-flat) and thus having calculated Measures for Profit, Revenue, Costs, the relevant variances, etc.

I think I have to create a calculated table, but I have no idea how to do so. A simple example of what I'm basically trying to achieve is shown below. Can anybody help?

Many thanks!

[bswylie · Accepted solution]

I don't know if this is useful to anybody but I finally figured out how to get this done. May be a bit trivial for some users here but it may also be useful for some others so thought I'd post my solution.

It's probably not the optimal way of doing it but it seems to be working perfectly. Open to any suggestions for improvement so I can learn how to do this better in the future.

Regards

Store Profit Waterfall Test PBIX Model

[bretto]

Hey - the file is gone. I have this same issue. How did you do it?

[bswylie]

Sorry for the slow reply, I've been away. I think you couldn't access the file because Dropbox changed the public folder to private for all accounts if I remember correctly.

Here's a link to what I think was the model - give it a try?

https://www.dropbox.com/s/rnbzf5dp8eec0vj/Store%20Profit%20Test%20v2.pbix?dl=0