cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rewind
Helper I
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...

 

IdentifierScoresWeight
10.674%
20.674%
30.6319%
40.624%
50.5920%
60.334%
70.148%
80.1111%
905%
1002%

 

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
OwenAuger
Super User I
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
Connect on Twitter
Connect on LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User I
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
Connect on Twitter
Connect on LinkedIn

View solution in original post

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors