cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
haferguson24
New Member

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.

haferguson24_1-1631811200672.png

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:

 

haferguson24_5-1631819085290.png

 

Thanks so much in advance for your time helping!

1 ACCEPTED SOLUTION
amitchandak
Super User
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

 

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

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yingjl
Community Support
Community 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.

amitchandak
Super User
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

 

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

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.