Subtracting two rows in Power BI

[NickT]

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

Qtr1             50,000      £100,000       £60,000           £40,000             £2                   £1.20            £0.80 

Qtr2             80,000      £120,000       £80,000           £40,000             £1.50              £1.00            £0.50

I want to calulate the difference between the two data sets so I can explain the margin movement between the two periods.

I want to automate the explanation for the volume variance, price variance and cost variance

Once I have explained the movement between volume, sales price and cost I want to slice on the data to slice it further by sales region, product category, salesman etc.

Any advice would be very much appreciated.

Kind regards

NickT 

[Anonymous]

Not sure how would like to present. Could you please explain the expected output.

Thanks
Raj

[NickT]

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

[NickT]

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