cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pamboys09 Regular Visitor
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

CaseCountTimeStampBatchLine
601/22/20 8:44 AMBatch 131
601/22/20 8:43 AMBatch 107
601/22/20 8:42 AMBatch 55
601/22/20 8:42 AMBatch 122
601/22/20 8:40 AMBatch 93
601/22/20 8:40 AMBatch 131
601/22/20 8:39 AMBatch 44
601/22/20 8:38 AMBatch 118
601/22/20 8:37 AMBatch 55
601/22/20 8:37 AMBatch 131
601/22/20 8:36 AMBatch 107
601/22/20 8:35 AMBatch 44
601/22/20 8:34 AMBatch 22
601/22/20 8:33 AMBatch 93
601/22/20 8:32 AMBatch 55
601/22/20 8:32 AMBatch 131
601/22/20 8:32 AMBatch 118
601/22/20 8:28 AMBatch 131
601/22/20 8:28 AMBatch 44
601/22/20 8:27 AMBatch 93
601/22/20 8:27 AMBatch 107
601/22/20 8:27 AMBatch 22
601/22/20 8:27 AMBatch 55
601/22/20 8:25 AMBatch 131
601/22/20 8:24 AMBatch 44
601/22/20 8:23 AMBatch 118

 

Detail Batch

SizeWeightBatchCode
285Batch 1E
403Batch 3H
183Batch 8B
403Batch 2H
183Batch 7B
363Batch 4G
323Batch 5F
323Batch 6F
363Batch 9G
363Batch 11G

 

Efficiency Calculation

SizeWeightCase per minuteCode
15152A
18152B
21152C
24152D
28152E
32152F
36152G
40152H
44152I
15252A
18252B
21252C
24252D
28252E
32252F
36252G
40252H
44252I
15352A
18352B
21352C
24352D
28352E
32352F
36352G
40342H
44352I
15542A
18542B
21542C
24542D
28542E
32542F
36542G
40538H
44538I
15732A
18732B
21732C
24732D
28732E
32732F
36732G
40732H
44732I

 

here's the final result should look like

Capture2312.PNG

 

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

2 REPLIES 2
Microsoft v-alq-msft
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:

f1.png

 

Best Regards

Allan

 

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

pamboys09 Regular Visitor
Regular Visitor

Re: Efficiency Calculation - Need Help

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

Capture23232.PNG

 

312312312312.PNG

 

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 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors