0

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.

Peter
  • 10,959
  • 2
  • 30
  • 47
Paulo Maciel
  • 27
  • 3
  • Hi,guys. I appreciate help formatting the tables (I promise improve over time). About the problem, I solved the problem. The steps I made: FIRST - I summarized the production table using Summarize(DateProduction, SKU, Shift, Count(SKU)) SECOND - I made Planned table to have the same columns and order that the summarized production THIRD - I set NO relationships between the 2 tables FOURTH - I created the final table_Adherency by using SelectColumns for each table and a NaturalLetfOuterJoin to make the table of interest FIFTH - I created a measure to %Adherency It worked! – Paulo Maciel Dec 19 '22 at 14:52
  • I'm glad you found the solution. You can post your answer bellow. – Marco Aurelio Fernandez Reyes Dec 21 '22 at 14:52

1 Answers1

1
  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.

Peter
  • 10,959
  • 2
  • 30
  • 47
  • Thanks for helpimg, Peter. I've tried here and worked perfetctly. – Paulo Maciel Dec 19 '22 at 18:04
  • Hi @Peter. I've found an issue using this solution. I always get the production planned when the production executed is equal to 0. But I cannot see when the production executed is > 0 and planned = 0. That happens, for example, when part of a planned production is delayed to the next shift. I appreciate any help. – Paulo Maciel Feb 06 '23 at 18:13