Weighted Average

[zfraz]

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!

[v-yulgu-msft · Accepted 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