Power Pivot - Calculating a Defect Rate

Question

• Table 1 (DATA): Lists each held product for all of 2016
• Table 2 (PRD DATA): Lists total good parts produced for each product in 2016
• Table 3 (Label): Links the two tables based on the individual product number

In a normal excel pivot table I would create a calculated column that does the following: Total Held / (Total Production + Total Held) to get a defect percentage rate by product.

How do I do that in Power Pivot?

The example below, the Calculation (column H) is what I am trying to make Power Pivot provide.

Answer 1

I would first create relationships between the tables, using the key columns. Hopefully you can figure that out, otherwise post more details.

With the relationships in place, you will be able to write essentially the same calculation, e.g.

Calculation =
DIVIDE (
    SUM ( 'DATA'[Held Quantity] ),
    SUM ( 'PRD DATA'[Qty Prod] ) + SUM ( 'DATA'[Held Quantity] ),
    0
)

I recommend the DIVIDE function to avoid "divide by zero" errors.