Need advice on Matrix Table with Two tables with calulations

[Anonymous]

Hi

I need some advice to calulate supplier PPM from two tables. The calulation is as followed - Defects / Qty Received * 1000000 = Supplier Code PPM from the two tables below. I have a sample format report (under the table image) by month that I am trying to recreate  using Power Bi Matrix table but been unsuccessful. Any guidance would be helpful.

[Anonymous · Accepted solution]

Hi @Anonymous ,

I can find the supplier code as relationship key, but it seems like lots of records has same supplier code. Is there any other fields to help records correct mapping?

If your requirement is for whole records, you can try to use following formula:

Measure =
CALCULATE (
    SUM ( TableB[Defects] ),
    FILTER (
        ALLSELECTED ( TableB ),
        [Supplier Code] IN VALUES ( TableA[Supplier Code] )
    )
)
    / CALCULATE (
        SUM ( TableA[Defects] ),
        ALLSELECTED ( TableA ),
        VALUES ( TableA[Supplier Code] )
    ) * 1000000

Regards,

Xiaoxin Sheng