Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to calculate planned efficiencies for production data but am having trouble figuring out how to get the weighted averages based on the standards. I am trying to calculate what % of the day we ran a certain sku to be able to weight the standard efficiency for that product. See data example below:
Date | Item | Run Hours | LBS | STD Lbs | STD EFF |
5/15/2017 | 123 | 7 | 650 | 700 | 60 |
5/15/2017 | 120 | 7 | 1300 | 1400 | 70 |
5/15/2017 | 121 | 5 | 1400 | 1500 | 80 |
5/16/2017 | 123 | 9 | 800 | 900 | 60 |
5/16/2017 | 120 | 10 | 1975 | 2000 | 70 |
5/17/2017 | 121 | 4 | 1525 | 1600 | 80 |
5/17/2017 | 120 | 15 | 3300 | 3000 | 70 |
The STD Lbs and STD EFF are lookups from the standards table in power bi. The first four columns are loaded from an Access database housing production data. I need to figure out the planned eff dynamically.
Ex. On 5/15/17 the total run hours were 19 and we should have had a planned eff of 72.89 based on the weighted run times over the day, ie. 72.89=(7/19*60)+(7/19*70)+(5/19*80).
How can I write a measure for Power BI to do this math for me?
Thanks!
Solved! Go to Solution.
Hi @zfraz,
In above table, you can create two calculated columns referring to below DAX formula:
planned eff for each item = ( 'Weighted Average'[Run Hours] / ( CALCULATE ( SUM ( 'Weighted Average'[Run Hours] ), ALLEXCEPT ( 'Weighted Average', 'Weighted Average'[Date] ) ) ) ) * 'Weighted Average'[STD EFF] planned eff = CALCULATE ( SUM ( 'Weighted Average'[planned eff for each item] ), ALLEXCEPT ( 'Weighted Average', 'Weighted Average'[Date] ) )
Best regards,
Yuliana Gu
Hi @zfraz,
In above table, you can create two calculated columns referring to below DAX formula:
planned eff for each item = ( 'Weighted Average'[Run Hours] / ( CALCULATE ( SUM ( 'Weighted Average'[Run Hours] ), ALLEXCEPT ( 'Weighted Average', 'Weighted Average'[Date] ) ) ) ) * 'Weighted Average'[STD EFF] planned eff = CALCULATE ( SUM ( 'Weighted Average'[planned eff for each item] ), ALLEXCEPT ( 'Weighted Average', 'Weighted Average'[Date] ) )
Best regards,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |