CODEWITHSUNDEEP
powerbidaxpowerbi-desktopsummarizestar-schema
Paulo Maciel
Paulo Maciel

Reputation: 29

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.

Upvotes: 0

Views: 202

Answers (1)

Peter
Peter

Reputation: 12365

  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.

Upvotes: 1

Related Questions