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

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.