Need a little help with aggregates and column formulae

[kwpbi]

Hi,

I'm new to Power BI. I have decent experience with Excel and VBA, but this is obviously quite different! Here is what I am trying to do, and I feel confident that it can be done simply, I just can't seem to find out how.

I have two tables that each have two columns. The data for each table comes from two separate sources, and I want to combine them to develop scrap-percentage metrics.

Table 1 has columns "Work Order" and "Scrap Qty". This table only contains the work orders that had >0 parts scrapped.

Table 2 has columns "Work Order" and "Good Qty". This table contains the good quantities for EVERY work order.

In each table, there are rare instances of multiple rows with the same work order number. So the relationship between these tables is currently set to Many/Many with table 1 filtering table 2. I did it this way because I am after scrap percentage metrics, so I only care about looking at the work orders that had >0 scrapped parts (the work orders in table 1).

I now want to create a new column in table 1 that will bring in "Good Qty" data from table 2 for each work order found in table 1. To describe how this should work in words, if work order XYZ exists in table 1, find all instances of work order XYZ in table 2, sum up their “Good Qty” values, and enter that total into table 1's new column for work order XYZ. Do this for every row in table 1.

Your help is greatly appreciated! Thank you!

[jthomson]

I'd have thought this'd be easy enough to do in Power Query by grouping each of the tables on the work order number (summing on the quantity) and then merging the tables together?

[kwpbi]

Thanks for your reply. The only problem with that approach is that I do not want the "Scrap Qty" values in table 1 to be combined when there are more than one entry for the same work order. This is because I want to use this data for deeper analysis than just a top-level scrap percentage. Here's why:

The tables actually have more than two columns, but for simplicity of explaining my goal, I didn't elaborate. Each row also has work center data, and on occasion, the same work order might have qty's scrapped at more than one work center before it is packed/shipped (when packed/shipped, that is when "good qty" is recorded). For example, we scrapped 2 parts at the CNC machine, and another part in assembly. If I sum those up, I can no longer look at scrap percentage by work center.

So in summary, I only want to sum up those good qty's, becuase in a perfect world, there should only be one entry for those, but sometimes mistakes are made and transactions are corrected in the system with an additional entry (often a negative value). That is why they must be summed up to get an accurate "good qty". I hope this makes sense!

Thank you!

[kwpbi]

In case this helps with explanation or planning of a solution, If i were using excel to solve this problem, I would do the following:

1) Sum up all good qty's in table 2 for any reoccuring work order numbers (this is now my lookup table)

2) Create new column in table 1 that uses vlookup function to find correct good qty based on work order number.

Done. Super Simple. But in Power BI? I cannot figure this one out. It is an incredibly non-intuitive tool.

Thank you!