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.
Thanks Cmcmahan for your help!
I've tried your formula but the result is the same, may be I'm doing something wrong...
Resident RockstarIt 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.
It works now! thank you very much Cmcmahan for taking the time to helping me. I appreciate that.