Equivalent of =SUMPRODUCT?

[Rewind]

Looking for an PBI equivilant of sum product to work out weighted mean. Been stuck trouble shooting solutions and now I just think I'm overthinking this...

Identifier	Scores	Weight
1	0.67	4%
2	0.67	4%
3	0.63	19%
4	0.6	24%
5	0.59	20%
6	0.33	4%
7	0.14	8%
8	0.11	11%
9	0	5%
10	0	2%

Weighted Average (value im looking for) = 0.46

Average = 0.37

Bonus if it can be dynamic (e.g. user filters for identifier 1,2,3 and caculates weight and then weighted mean)

[OwenAuger · Accepted solution]

Hi @Rewind 

A measure like this should do the trick, assuming the weighting is to be done row-by-row over your table:

Weighted Average = 
VAR WeightedSum =
    SUMX ( 
        YourTable,
        YourTable[Scores] * YourTable[Weight]
    )
VAR TotalWeight = 
    SUM ( YourTable[Weight] )
RETURN
    DIVIDE ( WeightedSum, TotalWeight )

The measure iterates over your table, summing Scores * Weight, then divides by the total Weight.

This will also be dynamic in that it will respond to any filters you apply.

Regards,

Owen

[Rewind]

Thanks Owen, this solved my problem. Should have come here a few hours ago  😅