cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
moia79
Helper I
Helper I

Sum a column based on conditional criteria on two other tables

dear PowerBI masters,

 

I have a problem with a database, based on multiple tables. The data structure is as follows:

 

table 1 contains the list of "methods" and the respective "time labor", "time machine" and "grouping"

Metodshour laborhour machinegrouping
ABC1252
DEF2466
GHI1198

 

table 2 associates the methods to each product ID. One product ID is linked to one or more methods.

Product IDMethod
111ABC
111DEF
222DEF
222GHI
333ABC
333DEF
333GHI
444DEF

 

table 3 contains instead the quantities, by Product ID, month, year and classification. Little complexity, the same ID can have multiple rows with same classification, year and month.

Product IDClassificationYearMonthQuantity
111

 

I would need a resulting table that, for each "Method" in table 1 is able to sum the "Quantity" in table 3 - maintaining the classification, year and month - for all items that are linked to that "Method" in table 2, i.e.

 

Metodshour laborhour machinegroupingProduct IDClassificationYearMonthQuantity
ABC1252111
ABC1252333    
DEF2466111    
DEF2466222    
DEF2466444    
GHI1198222    
GHI1198333    

 

not sure what is the best way to do it. Can you please suggest? Should I create a new table combining the existing tables?

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @moia79 

 

You can establish the following relationship between 3 tables.

vzhangti_0-1668582756799.png

Result:

vzhangti_1-1668582797129.png

Is this the result you expect? If not, give examples of your desired outcome.

 

Best Regards,

Community Support Team _Charlotte

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
v-zhangti
Community Support
Community Support

Hi, @moia79 

 

You can establish the following relationship between 3 tables.

vzhangti_0-1668582756799.png

Result:

vzhangti_1-1668582797129.png

Is this the result you expect? If not, give examples of your desired outcome.

 

Best Regards,

Community Support Team _Charlotte

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

I created the relationships, but PBI allows me only to create multiple-to-multiple relationships.

 

I managed it to create two additional tables, by using the function "merge", and even if the results is a very long table, it works.

 

thanks

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors