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:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Key Figure[/TH]
[TH]UOM[/TH]
[TH]Month 1[/TH]
[TH]Month 2[/TH]
[TH]Month 3[/TH]
[TH]Month 4[/TH]
[TH]Month 5[/TH]
[TH]Month 6[/TH]
[TH]Month 7[/TH]
[TH]Month 8[/TH]
[TH]Month 9[/TH]
[TH]Month 10[/TH]
[TH]Month 11[/TH]
[TH]Month 12[/TH]
[TH]Average[/TH]
[/TR]
[TR]
[TD]Weight[/TD]
[TD]PUMs[/TD]
[TD]4,011,692[/TD]
[TD]4,186,301[/TD]
[TD]3,610,294[/TD]
[TD]3,413,225[/TD]
[TD]3,594,839[/TD]
[TD]3,684,366[/TD]
[TD]4,107,366[/TD]
[TD]3,765,739[/TD]
[TD]3,818,063[/TD]
[TD]3,691,572[/TD]
[TD]3,725,825[/TD]
[TD]3,204,528[/TD]
[TD]3,734,484[/TD]
[/TR]
[TR]
[TD]Value[/TD]
[TD]Days[/TD]
[TD]32.0[/TD]
[TD]32.8[/TD]
[TD]29.1[/TD]
[TD]27.3[/TD]
[TD]26.8[/TD]
[TD]27.6[/TD]
[TD]23.6[/TD]
[TD]21.6[/TD]
[TD]21.0[/TD]
[TD]22.8[/TD]
[TD]31.3[/TD]
[TD]29.3[/TD]
[TD]27.110[/TD]
[/TR]
</tbody>[/TABLE]

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