Efficiency Calculation - Need Help

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.