Forum Discussion
Challenge with Dataset Relationships and Multipying Values from 2 Datasets
I'm creating a Purchasing Dashboard that will total up the cost-savings on parts received each month. To do this, I have 2 sources:
1. Excel Spreadsheet that is Direct Query (on Shared Drive), used to add part numbers with cost-savings
2. SQL Server connection to ERP Purchase Receipts Table
I'm having a challenge with creating the appropriate relationship between variables. I am trying to link the Part Numbers on the Cost-Savings spreadsheet with the PURC_ORDER_LINE Part number. The only relationship that it will allow right now is Many to Many, with the PURC_ORDER_Line filtering Cost-Savings Spreadsheet.
My first question is, is that the correct relationship? It appears to be giving me the correct data when I combine and filter the data from the two tables.
But if it is the right relationship, then is there another reason I am having this issue:
For the data that matches, I want to multiply the value from the "Difference" column in the Cost-Savings Spreadsheet by the "Quantity" from the PURC_ORDER_LINE to calculate the cost-savings on each receipt.
This is the formula I'm entering:
So, can anyone help me get around this error to create a column that gives the product of the values in the last two columns:
Thanks so much in advance for your time helping!
haferguson24 , you should create a common part number dimension
Part = filter( distinct(union(distinct('COST-SAVINGS SPREADSHEET'[Part Number]),Distinct( 'PURC_ORDER_LINE'[Part ID]))), not(isblank([Part Number])) )
Then join them with part number and if for both tables
measure 2= sumx(values(Part[Partnumber]), calculate(Sum('COST-SAVINGS SPREADSHEET'[DIFFERENCE ]) *Sum( 'PURC_ORDER_LINE'[ORDER_QTY] ))
In place sum you can use max, min avg etc
or you can move the column to another table and then try
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8
3 Replies
- amitchandak
Super User
haferguson24 , you should create a common part number dimension
Part = filter( distinct(union(distinct('COST-SAVINGS SPREADSHEET'[Part Number]),Distinct( 'PURC_ORDER_LINE'[Part ID]))), not(isblank([Part Number])) )
Then join them with part number and if for both tables
measure 2= sumx(values(Part[Partnumber]), calculate(Sum('COST-SAVINGS SPREADSHEET'[DIFFERENCE ]) *Sum( 'PURC_ORDER_LINE'[ORDER_QTY] ))
In place sum you can use max, min avg etc
or you can move the column to another table and then try
refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8 
v-yingjlCommunity Support
Hi haferguson24 ,
For many-to-many relationship in Power BI Desktop, it could not allow to product values directly which would remind the error that you have faced.
You can try to create a bridge table based on this many-to-many relationship to split this relationship to two one-to-many relationships based on the two sources table then create measures to calculate values.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- Ashish_Mathur
Hi,
You should create a seperate table (say TableC) of unique Part ID and then create a relationship from the Part ID column of both tables with the Part ID column of TableC. Now in either of the 2 tables, write a calculated column formula using the LOOKUPVALUE() function to bring over the Difference or the Order_Qty column (as the case may be). You should now be able to use the SUMX() function as a measure.
