Can't get the correct calculation on second attribute

[TcT85]

Hi,

Description of issue.

I got two tables, one table contains the the bill of materials(BOM) of the product the second table that contain the defects.

When I correlate with "Product" column between the 2 tables, I can calculate the Yield and DPMO on the product level.

But when I want the Yield and DPMO on the Fruit level the calculations is getting completely wrong.

Data

BOM

Product	Fruit	Position	Total Pieces
A	Apple	A1	6
A	Apple	A2	6
A	Apple	A3	6
A	Apple	A4	6
A	Orange	O1	5
A	Orange	O2	5
A	Orange	O3	5
A	Orange	O4	5
A	Orange	O5	5
A	Strawberry	S1	2
A	Strawberry	S2	2
A	Strawberry	S3	2
A	Strawberry	S4	2
A	Strawberry	S5	2
A	Strawberry	S6	2
A	Tamarin	T1	3
B	Apple	A1	6
B	Apple	A2	6
B	Apple	A3	6
B	Strawberry	S1	2
B	Strawberry	S2	2
B	Strawberry	S3	2
B	Watermelon	W1	8
B	Watermelon	W2	8

Production

Product	SerialNumber	Fruit	Position	Piece	Defects	Defect Type
A	223355	Apple	A1	1	NG	Rotten
A	223355	Apple	A4	2	NG	Rotten
A	223355	Orange	O1	4	NG	Mishaped
A	223355	Orange	O2	5	NG	Rotten
A	223355	Orange	O5	6	NG	Mishaped
A	223355	Strawberry	S1	1	NG	Rotten
A	223355	Strawberry	S2	2	NG	Mishaped
A	223356				OK
A	223357				OK
A	223358				OK
A	223359				OK
A	223360				OK
A	223361				OK
A	223362				OK
A	223363				OK
A	223364				OK
A	223365				OK
A	223366				OK
A	223367				OK
A	333555	Apple	A2	2	NG	Rotten
A	333555	Apple	A2	2	NG	Mishaped
A	333555	Orange	O1	4	NG	Mishaped
A	333555	Orange	O2	5	NG	Rotten
B	445566	Apple	A1	5	NG	Rotten
B	445566	Apple	A3	1	NG	Mishaped
B	445566	Strawberry	S1	1	NG	Rotten
B	445566	Strawberry	S2	1	NG	Rotten
B	445566	Strawberry	S3	2	NG	Rotten
B	445567				OK
B	445568				OK
B	445569				OK
B	445570				OK
B	445571				OK
B	445572				OK
B	445573				OK

DAX formulas that is needed.

Fruit Yield

Fruit DPMO ( the Data that is highlithed in Bold text has 2 rows that should be calculated as one opportunity).

DAX formula that works.

Yield Product Defect =
VAR UniqeSerial =
CALCULATE(
DISTINCTCOUNT(Production[SerialNumber])
)
VAR DefectSerial =
CALCULATE(
DistinctCOUNT(Production[SerialNumber]),FILTER(Production,Production[Defects] ="NG")
)
VAR YieldDefect = DefectSerial/UniqeSerial
Return
1- YieldDefect

It's hard to explain in exact detail where it get's wrong, but please try these table and correlate with product.

[TcT85]

Here is where I get stuck.

When i try to calculate from BOM Table with Total Fruit used and with Total Defects Fruit, i cant get the crrect calculation on Total Defect Fruits. And vice versa with calculation from Production Table I get issue with Total Fruit.

[TcT85]

Sorry for asking again, could anyone help me with this issue? Im really stuck with this issue and can't continue build my report.

[TcT85]

Does anyone know if this is solvable? 

Or do I need to merge this into 1 table? I think it would work with this sample data, but now with my production data due to millions of row of data, the data above is just fictional.