Forum Discussion
Consolidate % using different weighting factor
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.
Resident RockstarFirst 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.
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]) )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
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.