Forum Discussion
Create a variance column in Power BI
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,
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.
2 Replies
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.