Create a variance column in Power BI

[Anonymous]

Hello, 

I have this data in excel and it is quite easy for me to do the calculation. 

The caluclation in the first cell under the Variance header is =IF(A2=A1,D2-D1,"")

Product	Year	Quarter	Sales Volume	Variance
Product 1	2015-16	Q1	100
Product 1	2015-16	Q2	120	20
Product 1	2015-16	Q3	90	-30
Product 1	2015-16	Q4	150	60
Product 2	2015-16	Q1	14
Product 2	2015-16	Q2	17	3
Product 2	2015-16	Q3	23	6
Product 2	2015-16	Q4	21	-2
Product 3	2015-16	Q1	220
Product 3	2015-16	Q2	130	-90
Product 3	2015-16	Q3	227	97
Product 3	2015-16	Q4	320	93

Basically i am trying to find the variance between the values for the same Product based on the year and quarter.

My data in power BI is set up in the same format however i cannot find if you can reference the above cell or not.

Any help would be great. 

Thanks,

[Anonymous · Accepted solution]

Hi @Anonymous ,

Do the following steps to achive the solution.

Create (add new column) Index Column from 0.

Create (add new column) Index Column from 1.

Merge Query with the same table, join the same table on Index 0 and Index 1 (see the attachement for reference).

Name the Merge Query as Next.

Expand columns Product and Sales Volumn.

Add a Custom Column = if [Next.Product]=[Product] then [Sales Volume]-[Next.Sales Volume] else null

This will give you the expected results, I have tested and it is working.

Let me know if you need any help.

Regards,

Pavan Vanguri.

[Anonymous]

Brilliant works perfectly!

Thanks very much.