cancel
Showing results for
Did you mean: Frequent Visitor

## Equivalent of =SUMPRODUCT?

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)

1 ACCEPTED SOLUTION  Super User I

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

Owen Auger

My Blog
2 REPLIES 2  Super User I

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

Owen Auger

My Blog Frequent Visitor

Thanks Owen, this solved my problem. Should have come here a few hours ago  😅 Announcements #### Welcome to the User Group Public Preview  