Forum Discussion
Helper IIICan't get the correct calculation on second attribute
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.
3 Replies
TcT85Does 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.
- 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
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.