cancel
Showing results for
Did you mean:
Helper I

## 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

Did I answer your question? Mark my post as a solution!

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

Did I answer your question? Mark my post as a solution!

My Blog
Helper I

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

Announcements

#### Manage your user group events

Check out the News & Announcements to learn more.

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors
Top Kudoed Authors