Challenge with Dataset Relationships and Multipying Values from 2 Datasets

[haferguson24]

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:

Measure 2 = 'COST-SAVINGS SPREADSHEET'[DIFFERENCE ] * 'PURC_ORDER_LINE'[ORDER_QTY]

But I'm getting this error:

A single value for column 'DIFFERENCE ' in table 'COST-SAVINGS SPREADSHEET' cannot be determined.

This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

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!

[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. 

[v-yingjl]

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.

[amitchandak · Accepted solution]

@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