powerbidaxpowerbi-desktopsummarizestar-schema

Calculation based on 2 fact tables (plan/actual comparison)

I have to link TWO tables (Planning vs Executed) that have: date, shift, SKU and Quantity to calculate the match between planned and executed (in %).
In other words, I need to create a comparison of two tables using 3 fields as reference (Date, Shift, SKU) and the comparison will use Programmed vs Executed production.

The tables I have are like these:

Planning Table

DateProg Shift SKU QtyProg
2022/12/01 1 ABC 12
2022/12/01 2 ABC 24
2022/12/01 1 JKL 35
2022/12/01 2 JKL 20
2022/12/02 1 ABC 15
2022/12/02 2 ABC 15
2022/12/02 1 JKL 33
2022/12/02 2 JKL 22

Production Table

DateProduction Shift SKU
2022/12/01 1 ABC
2022/12/01 1 ABC
2022/12/01 1 JKL
2022/12/01 1 JKL
2022/12/01 2 JKL
2022/12/01 2 JKL
2022/12/01 2 JKL
2022/12/01 2 ABC
2022/12/01 2 ABC
2022/12/01 2 JKL
2022/12/02 1 JKL
2022/12/02 1 JKL
2022/12/02 1 JKL
2022/12/02 2 ABC
2022/12/02 2 JKL
2022/12/02 2 JKL

For this sample data, my end result must be like this

DateProg SHIFT SKU QtyProg QtyExecuted %Adherency
2022/12/01 1 ABC 12 2 =2/12
2022/12/01 2 ABC 24 2 =2/24
2022/12/01 1 JKL 35 4 =4/35
2022/12/01 2 JKL 20 2 =2/20
2022/12/02 1 ABC 15 0 =0
2022/12/02 2 ABC 15 1 =1/15
2022/12/02 1 JKL 33 3 =3/33
2022/12/02 2 JKL 22 2 =2/22

I think it will take some steps to solve the complete problem.
But I will appreciate any help.

I tried to use DAX in a number of different ways. And up to now, none of a good result.

Solution
    1. I understand that every row in the Production Table stands for 1 Qty executed, so add a column
    QtyExecuted  = 1
    1. Add a Key column to the Production Table
    Key = 'Production Table'[DateProduction] & 'Production Table'[SKU] & 'Production Table'[Shift]
    1. Add a similar Key column to the Planning Table
    Key = 'Planning Table'[DateProg] & 'Planning Table'[SKU] & 'Planning Table'[Shift]
    1. Create a one-to-many relationship between 'Planning Table'[Key] and 'Production Table'[Key]
    2. Add QtyExecuted to the Planning Table
    QtyExecuted = SUMX(RELATEDTABLE('Production Table'), 'Production Table'[QtyExecuted])
    1. Add %Adherency to the Planning Table
    % Adherency = DIVIDE('Planning Table'[QtyExecuted], 'Planning Table'[QtyProg])

    The resulting table should look like this:

    enter image description here

    Note that your expected data is wrong with regards to the JKL production on 2022/12/01.