Consolidate % using different weighting factor

[Reuben]

Hi experts,

I’m struggeling with the calculation of the Utilization Rate. What in excell is very easy to calculate in PBI is becaming very challengin

There are many sites with many production lines. What I want to do is to calculate the % of Utilization Rate for each individual production line, and after that:

1. Consolidate at a site level using [Opening Time] x [Speed units hour] as a weighing factor, and then
2. Consolidate at a company level using [unidades producidas] as a weighting factor.

(see attached the calculation in excell)

Below i wil explain how is built the model and the mesures that I have created:

There is a table ‘PartesProduccionLineas’ with the following columns (among others that are not affected by this issue):

[Planta] -> Production Site

[Linea] -> Combination of Production Site & Line

[Referencia articulo] -> SKU

[Minutos] -> Time (in minutes) that the production line is open, what we call Opening time

[Speed/hour] -> Theorical production per hour

[Unidades producidas] -> units produced

And the following measures:

Mesure 1

Opening Time H = DIVIDE(sum(PartesProduccionLineas[Minutos]);60) –> To convert minuts to hoursMesure 2

Speed units hour = sumx(PartesProduccionLineas;[Speed/hour]*[Opening Time H])/[Opening Time H]-> To calculate the average Speed/hour by using the Opening Time as a weighting factor.

Mesure 3

Utilization Rate =

VAR ReferenceTime = 240*8*2 -> Total theorical working hours

VAR UtilizationRate = divide([Opening Time H];divide(ReferenceTime;12)*If(ISCROSSFILTERED(Calendario[Calendar Month]);DISTINCTCOUNT(Calendario[Calendar Month]);0)) -> The reference time is divided in 12 month and multipliyed by the number of month selected.

Return

divide(UtilizationRate*[Opening Time H]*[Speed units hour];[Opening Time H]*[Speed units hour])

Here is my result and the desired output:

https://www.dropbox.com/s/59sf7zg6s82sx1q/Excel%20calculation%20UR.xlsx?dl=0

As you can see, the individual line utilization rate is correctly calculated but then when I want to do the consolidation I am a Little bit lost.

Any idea is wellcome.

Thank you very much in advance.

[Cmcmahan]

First things first, I don't think your current measures are working the way you are expecting.

I'm guessing that you could replace your current [Speed units hour] with this measure, and see no difference:

Speed units hour (simple) = SUM(PartesProduccionLineas[Speed/hour])

And that you could replace your current utilization rate with this and get the same results (within a single month. Your ISCROSSFILTERED code helps when multiple months are selected):

Utilization Rate (simple) =
VAR ReferenceTime = 240*8*2 // Total theorical working hours
RETURN
[Opening Time H] / (ReferenceTime/12)

This is just due to you multiplying by extra fields and then immediately dividing by the same value.  You aren't actually weighting either [speed units hour] or [Utilization Rate].

However, it's your Utilization rate that's not giving the correct result, so we want to make a change there.

The best way to get the correct sum of groups that doesn't just use the entire group sum for the calculation is to use a SUMX function with a SUMMARIZE'd table as the input.  

Actual Utilization Rate = 
VAR ReferenceTime = 240*8*2* IF(ISCROSSFILTERED(Calendario[Calendar Month]);DISTINCTCOUNT(Calendario[Calendar Month]);0) // Total theoretical working hours
VAR UtilizationRate = divide([Opening Time H];divide(ReferenceTime;12))
RETURN
DIVIDE( 
    SUMX( SUMMARIZECOLUMNS( PartesProduccionLineas[Line];"Numerator"; [Operating Time H] * [Speed units hour] * UtilizationRate); [Numerator]);
    SUMX( SUMMARIZECOLUMNS( PartesProduccionLineas[Line];"Denominator"; [Operating Time H] * [Speed units hour]); [Denominator])
)

Add this measure in your table in place of [Utilization Rate], and you should get the correct result in your total rows.

[Reuben]

Thanks @Cmcmahan for your help!

I've tried your formula but the result is the same, may be I'm doing something wrong...

[Cmcmahan]

It took some doing, but I was able to figure out the problem.  When you use VAR, it calculates the value for UtilizationRate once, when the measure is first called. 

If you explicitly calculate the UtilizationRate within the SUMX function, it works perfectly.

Actual Utilization Rate = 
VAR ReferenceTime = 240*8*2/12 * IF(ISCROSSFILTERED(Calendario[Calendar Month]);DISTINCTCOUNT(Calendario[Calendar Month]);0) // Total theoretical working hours
RETURN
DIVIDE( 
    SUMX( PartesProduccionLineas; [Opening Time H] * [Speed Units/Hour] * divide([Opening Time H]; ReferenceTime) );
    SUMX( PartesProduccionLineas; [Opening Time H] * [Speed Units/Hour])
)

[Reuben]

Hi @Cmcmahan 

It´s not working either. Based on your formula I've tried a variant of the function, but I came up with my very first result.

I am attaching an abbrevating model in case it helps...

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

Thanks!

[Cmcmahan · Accepted solution]

The abbreviated model was VERY helpful!

I got it working with this measure. The changes are highlighted in blue:

Actual Utilization Rate = 
VAR ReferenceTime = 240*8*2/12 
RETURN
DIVIDE( 
    SUMX( VALUES(PartesProduccionLineas[Linea]), [Opening Time H] * [Speed units hour] * [Opening Time H]/320) ,
    SUMX( VALUES(PartesProduccionLineas[Linea]), [Opening Time H] * [Speed units hour] )
)

The issue was that it was aggregating by each entry in the table, instead of by the entire line.  Now that it's grouping properly, we get the expected result.

[Reuben]

It works now! thank you very much @Cmcmahan for taking the time to helping me.  I appreciate that.