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.

MBAS on Demand

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.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.