Efficiency Calculation - Need Help

[Anonymous]

Hello Guys,

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

(Direct Query)

Case Count SQL

CaseCount	TimeStamp	Batch	Line
60	1/22/20 8:44 AM	Batch 13	1
60	1/22/20 8:43 AM	Batch 10	7
60	1/22/20 8:42 AM	Batch 5	5
60	1/22/20 8:42 AM	Batch 12	2
60	1/22/20 8:40 AM	Batch 9	3
60	1/22/20 8:40 AM	Batch 13	1
60	1/22/20 8:39 AM	Batch 4	4
60	1/22/20 8:38 AM	Batch 11	8
60	1/22/20 8:37 AM	Batch 5	5
60	1/22/20 8:37 AM	Batch 13	1
60	1/22/20 8:36 AM	Batch 10	7
60	1/22/20 8:35 AM	Batch 4	4
60	1/22/20 8:34 AM	Batch 2	2
60	1/22/20 8:33 AM	Batch 9	3
60	1/22/20 8:32 AM	Batch 5	5
60	1/22/20 8:32 AM	Batch 13	1
60	1/22/20 8:32 AM	Batch 11	8
60	1/22/20 8:28 AM	Batch 13	1
60	1/22/20 8:28 AM	Batch 4	4
60	1/22/20 8:27 AM	Batch 9	3
60	1/22/20 8:27 AM	Batch 10	7
60	1/22/20 8:27 AM	Batch 2	2
60	1/22/20 8:27 AM	Batch 5	5
60	1/22/20 8:25 AM	Batch 13	1
60	1/22/20 8:24 AM	Batch 4	4
60	1/22/20 8:23 AM	Batch 11	8

Detail Batch

Size	Weight	Batch	Code
28	5	Batch 1	E
40	3	Batch 3	H
18	3	Batch 8	B
40	3	Batch 2	H
18	3	Batch 7	B
36	3	Batch 4	G
32	3	Batch 5	F
32	3	Batch 6	F
36	3	Batch 9	G
36	3	Batch 11	G

Efficiency Calculation

Size	Weight	Case per minute	Code
15	1	52	A
18	1	52	B
21	1	52	C
24	1	52	D
28	1	52	E
32	1	52	F
36	1	52	G
40	1	52	H
44	1	52	I
15	2	52	A
18	2	52	B
21	2	52	C
24	2	52	D
28	2	52	E
32	2	52	F
36	2	52	G
40	2	52	H
44	2	52	I
15	3	52	A
18	3	52	B
21	3	52	C
24	3	52	D
28	3	52	E
32	3	52	F
36	3	52	G
40	3	42	H
44	3	52	I
15	5	42	A
18	5	42	B
21	5	42	C
24	5	42	D
28	5	42	E
32	5	42	F
36	5	42	G
40	5	38	H
44	5	38	I
15	7	32	A
18	7	32	B
21	7	32	C
24	7	32	D
28	7	32	E
32	7	32	F
36	7	32	G
40	7	32	H
44	7	32	I

here's the final result should look like

https://drive.google.com/open?id=1YgzrSNHR5Upbd0m10tODAFujnLAMA5JM 

[v-alq-msft]

Hi, @Anonymous 

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.

Then you may create three measures as follows.

input = 
var _input = 
SUMX(
    'Detail batch',
    'Detail batch'[Weight]*
    SUMX(
        RELATEDTABLE('Case Count SQL'),
        'Case Count SQL'[CaseCount]
    )
)
return _input

target = 
var _currentline = MIN('Case Count SQL'[Line])
var _currentcode = MIN('Detail batch'[Code])
var _currentweight = MIN('Detail batch'[Weight])
var _totalminutes = 
COUNTROWS(
    FILTER(
        ALL('Case Count SQL'),
        'Case Count SQL'[Line] = _currentline
        )
)

var _caseperminute =
LOOKUPVALUE(
    'Efficiency Calculation'[Case per minute],
    'Efficiency Calculation'[Code],_currentcode,
    'Efficiency Calculation'[Weight],_currentweight
)

return
4*_totalminutes*_caseperminute


Efficiency = [input]/[target]

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-alq-msft 

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

https://www.dropbox.com/s/5irun465kv0ea58/test%20test.pbix?dl=0