cancel
Showing results for
Did you mean:
Regular Visitor

## Efficiency Calculation - Need Help

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

2 REPLIES 2
Microsoft

## Re: Efficiency Calculation - Need Help

Hi, @pamboys09

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.

Highlighted
Regular Visitor

## Re: Efficiency Calculation - Need Help

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

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.