Weighted average DAX help...

[Matty]

Hi Team,

Looking to replicate the following in DAX...

Here's an example table in Excel to paint the picture:

Key Figure	UOM	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12	Average
Weight	PUMs	4,011,692	4,186,301	3,610,294	3,413,225	3,594,839	3,684,366	4,107,366	3,765,739	3,818,063	3,691,572	3,725,825	3,204,528	3,734,484
Value	Days	32.0	32.8	29.1	27.3	26.8	27.6	23.6	21.6	21.0	22.8	31.3	29.3	27.110

<tbody>
</tbody>

I would like to calculate the weighted average value seen in cell O3, which was achieved in Excel using the following:

=AVERAGE(C3:N3*C2:N2)/AVERAGE(C2:N2)

Array entered.

Hope someone can assist. I've been playing around for a while and can't quite get it.

Thanks,

Matty

 

[gazpage]

Usually weighted averages look like:

WAvg:=
DIVIDE (
    SUMX (
        VALUES ( Date[Months] )
        CALCULATE ( SUM ( Data[Value] ) * SUM ( Data [Weighting] ) )
    ),
    SUM ( Data[Weighting] )
)

 

[Matty]

Hi Gaz,

Thanks for that. I managed to get something to work based on this.

Cheers,

Matty