My brain is fried on this calculation, badly need help.
So i have this column below and I attached a sample PBIX file.
So here's the struggle. Our formula is very simple but for some reason its hard to translate it to powerbi. (i can do it on excel)
We measure efficiency by hour by line.
Efficiency = input / target Input = CaseCount(CaseCountSQL table) x Weight(DetailBatch Table) <---- needs relation or lookup(i guess) Target = 4 x Total Minutes x (Case per minute) <-- case per minute is the challenging part because it needs to match the size and code
Based on your description, case per minute is the challenging part because it needs to match the size and code. However, I can not find 'size' column in 'Efficiency Calculation' table. So I guess it needs to match the weight and code.
Sorry I forgot to put the sizes. I attached the file with the PBIX plus the stuff that I checked. I can feel that we are close.
So i tried to breakdown your measure and create some comparison with my excel sheet. Minutes - this is very tricky for me, because for every hour, there should be 60 minutes, unless its the MIN and MAX for the DAY. Example on Batch 1 Hour 5, it only have 33 mins since Start time is 5:27 AM (60 - 27 = 33 mins used), and the one im having trouble/challenge is if theres two or more Batches in an hour(see highlighted in yellow below), in this case we just divide the Minutes used by # of Batches.
Case per Minute - is based on two criteria, weight,size and code (from Detail Batch and Efficiency Calculation) (if theres two batches in an hour, just use the Case per minute of the first Batch(earliest?(not sure if available on direct query)).