Forum Discussion
Helper IIHow to do VLOOKUP functions in Power BI?
Super Userkwpbi your question is still not very clear, it is always good idea to show some data sample. based on your input i think this is how your dataset look like
Table1 -> This doesn't have duplicate work order number, correct?
WO Scrap Qty
1 100
2 200
3 300
Table2 -> This has duplicate work order number, correct?
WO Good Qty
1 100
1 200
1 300
2 200
3 300
3 400
End result you are looking for
WO SCrap Qty Good Qty (sum of good qty from table 2 for each WO)
1 100 600
2 200 200
3 300 700
Is above correct understanding what you are looking for?
Helper IIThank you for the prompt response.
The only correction is that there are duplicate work order #'s in BOTH tables. But in table 1, I do not want to combine them. Here's some more info:
Table 1 data only contains work orders that had >0 parts scrapped. There can be several entries for one work order # because parts may have been scrapped at more than one work center before the order was completed. I do not want to sum these rows together because I will lose that work center data.
Table 2 contains EVERY work order. There should only be one "good qty" for each work order, but mistakes get made and corrected on occasion, resulting in duplicate transactions (these need to be combined).
So in summary, step 1 is to combine those good quantities so there are no duplicates in table #2. Step 2 is to simply add those good values to a new column in table #1 by matching up the work order #, without combining the duplicates in table 1.Here is an example:
Table #1 (original)
Work Order # Scrap Qty
5551 3
5551 1
5552 1
5554 4
5554 1
Table #2 (original)
Work Order # Good Qty
5550 12
5551 36
5551 -8
5552 15
5553 21
5554 60
New Table #2 (duplicates combined)
Work Order # Good Qty
5550 12
5551 28
5552 15
5553 21
5554 60
New table #1 (with good qty column added)
Work Order # Scrap Qty Good Qty
5551 3 28
5551 1 28
5552 1 15
5554 4 60
5554 1 60
I hope that isn't too much info!
Thanks again for your help.
- jdbuchanan71
If you pull in the work order # from the combined table and the scrap # from the scrap column of the scrap table and set that field to 'do not summarze' and the [Good Amount] measure you should get what you are looking for. It will repeate the full good amount on each work order line but the srap amount will be ech entry from the scrap table.
- parry2k
kwpbi you need to set many to many relationship between table 1 and table 2 on Work order. As far as you don't care about work order which doesn't exists in table 1 but table 2 (for example 5550), solution will work.
- kwpbi
parry2k wrote:kwpbiyou need to set many to many relationship (for example 5550), solution will work.
It already is a many-to-many relationship (can't be anything else).
I'm not sure where to start in Power BI to accomplish this task. The logical first step, as I have mentioned, is to create a new table that totals up all duplicate work order rows in my "good qty" table. Then I believe I should be able to create a one-to-many relationship between that new table (with the duplicates combined) and my "scrap qty" table to get the new column I am after. Does that sound right?
If so, then maybe all I need help with is how to create that new table where the duplicate work orders have been combined?
thanks.
- kwpbi
jdbuchanan71 wrote:
If you pull in the work order # from the combined table and the scrap # from the scrap column of the scrap table and set that field to 'do not summarze' and the [Good Amount] measure you should get what you are looking for. It will repeate the full good amount on each work order line but the srap amount will be ech entry from the scrap table.
I'm sorry but I don't really understand what you are describing, or how to do it. If I had more experience with Power BI it might make more sense but at this point I wouldn't know where to start (how to make that "combined table" for example).
- parry2k
kwpbi here are the steps;
- add table visual
- put work order number from table 1 on values
- put scrap qty from table 1 on values, and there is arrow key next to scrap qty on values, click that, and choose don't summarize
- put good qty from able 2 on values
you will get the result.
if it doesn't work then tell step by step what you did and what you are getting
