Forum Discussion
Subtracting two rows in Power BI
I have sales data, cost data and hence margin data for two different periods. Period Volume Sales Cost of sales Margin Unit selling price Unit cost Unit margin ...
Hi Raj
What I am looking to achieve is a waterfall chart as follows:
Opening % margin
Volume variance
Price variance
Cost variance
Closing % margin
I then want to then slice this graph by sales region, portfolio category, sales channel etc. to interrogate the data further.
The interrogation will be carried out by a series of slicers set against the base data in the waterfall chart.
In the background I will have a data tab with the supporting backing data.
I hope this helps.
Kind regards
Nick
| Volume period 1 | Volume period 2 | Gross price period 1 | Gross price period 2 | Discount period 1 | Discount period 2 | Cost period 1 | Cost period 2 | Net price period 1 | Net price period 2 | Margin period 1 | Margin period 2 | Gross price per unit period 1 | Gross price per unit period 2 | Discount per unit period 1 | Discount per unit period 2 | Net Price per unit period 1 | Net Price per unit period 2 | Unit margin period 1 | Unit margin period 2 | Cost per unit period 1 | Cost per unit period 2 | Period 1 margin % | Period 2 magin % | Volume difference | Discount difference | Price diference | Cost difference | Check | Volume difference % | Discount difference % | Price difference % | Cost difference % | % Check |
| 50 | 60 | £1,000.00 | £1,100.00 | £100.00 | £130.00 | 800 | 825 | £900.00 | £970.00 | £100.00 | £145.00 | £20.00 | £18.33 | £2.00 | £2.17 | £18.00 | £16.17 | £2.00 | £2.42 | £16.00 | £13.75 | 11% | 15% | £20.00 | -£10.00 | -£100.00 | £135.00 | £0.00 | 2% | -1% | -9% | 12% | 4% |
| Gross price less discount | Net price less cost | Gross price divided by volume | Discount divided by volume | Net price divided by volume | Margin divided by volume | Cost divided by volume | Margin divided by net price | Difference in volume times unit margin period 1 | Difference in unit discount times period 2 volume | Difference in unit price times period 2 volume | Difference in unit cost times period 2 volume | Volume difference / margin movement times margin % change | Discount difference / margin movement times margin % change | Price difference / margin movement times margin % change | Cost difference / margin movement times margin % change |
I AM TRYING TO REPLICATE TEH ABOVE CALCULATIONS IN POWER BI