Finding row wise variances from columns from two separate tables

[Anonymous]

Apologies about the weird question. I just didn't know how to word it properly. 

I have two tables one for older products and one for future products. I would like to create a variance table that shows the difference in Cost and Selling Price when we make the change from our Old Product to New Product with all possible permuation (A to X, A to Y, A to Z; B to X, B to Y, B to Z) etc. as the row fields.

Old Product table

Product	Cost per tonne	Selling Price
A	15	1000
B	25	2000
C	30	1000

New Product Table

Product	Cost per Tonne	Selling Price
X	22	900
Y	27	1500
Z	33	2000

Variance Table

Old Product	New Product	Diff in Cost per Tonne	Difference in Selling Price
A	X	7	-100
B	Y	2	-500
C	Z	3	1000
A	Y	-12	-500
A	Z	-8	0
B	X	3	1100
B	Z	-8	0

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to the data you provide, you first need to use the CROSSJOIN function to create a new table, and then create two measures to calculate the difference between old_Selling Price and new_Selling Price, old_Cost per tonne and new_Cost per Tonne. You can refer to the screenshot below:

Here is the sample pbix file.

If the problem is still not resolved, please provide detailed error information and let me know immediately, looking forward to your reply.

Best Regards,

Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can create a new Table like

addcolumns(crossjoin(selectcolumns('Old Product', "Old Product",[Product] "Old Cost per" [Cost per tonne],"Old Selling Price" [Selling Price]), 'New Product')
,"Price Diff" , [Cost per Tonne] - [Old Cost per] )

Add more columns as per need