cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Finding row wise variances from columns from two separate tables

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

ProductCost per tonneSelling Price 
A151000
B252000
C301000

New Product Table

ProductCost per TonneSelling Price
X22900
Y271500
Z332000

 

Variance Table

Old ProductNew ProductDiff in Cost per TonneDifference in Selling Price
AX7-100
BY2-500
CZ31000
AY-12-500
AZ-80
BX31100
BZ-80
1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @stiggy771 ,


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:

 

v-henryk-mstf_0-1610673961489.jpeg

v-henryk-mstf_1-1610673968036.png

v-henryk-mstf_2-1610673978409.png

v-henryk-mstf_3-1610673984685.png

 

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.

View solution in original post

2 REPLIES 2
Microsoft
Microsoft

Hi @stiggy771 ,


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:

 

v-henryk-mstf_0-1610673961489.jpeg

v-henryk-mstf_1-1610673968036.png

v-henryk-mstf_2-1610673978409.png

v-henryk-mstf_3-1610673984685.png

 

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.

View solution in original post

Super User IV
Super User IV

@stiggy771 , 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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors