topic Equivalent of =SUMPRODUCT? in Desktop
https://community.powerbi.com/t5/Desktop/Equivalent-of-SUMPRODUCT/m-p/1788318#M698745
<P>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...</P><P> </P><TABLE border="1"><TBODY><TR><TD>Identifier</TD><TD>Scores</TD><TD>Weight</TD></TR><TR><TD>1</TD><TD>0.67</TD><TD>4%</TD></TR><TR><TD>2</TD><TD>0.67</TD><TD>4%</TD></TR><TR><TD>3</TD><TD>0.63</TD><TD>19%</TD></TR><TR><TD>4</TD><TD>0.6</TD><TD>24%</TD></TR><TR><TD>5</TD><TD>0.59</TD><TD>20%</TD></TR><TR><TD>6</TD><TD>0.33</TD><TD>4%</TD></TR><TR><TD>7</TD><TD>0.14</TD><TD>8%</TD></TR><TR><TD>8</TD><TD>0.11</TD><TD>11%</TD></TR><TR><TD>9</TD><TD>0</TD><TD>5%</TD></TR><TR><TD>10</TD><TD>0</TD><TD>2%</TD></TR></TBODY></TABLE><P> </P><P>Weighted Average (value im looking for) = 0.46</P><P>Average = 0.37</P><P> </P><P>Bonus if it can be dynamic (e.g. user filters for identifier 1,2,3 and caculates weight and then weighted mean)</P>Fri, 16 Apr 2021 05:09:36 GMTRewind2021-04-16T05:09:36ZEquivalent of =SUMPRODUCT?
https://community.powerbi.com/t5/Desktop/Equivalent-of-SUMPRODUCT/m-p/1788318#M698745
<P>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...</P><P> </P><TABLE border="1"><TBODY><TR><TD>Identifier</TD><TD>Scores</TD><TD>Weight</TD></TR><TR><TD>1</TD><TD>0.67</TD><TD>4%</TD></TR><TR><TD>2</TD><TD>0.67</TD><TD>4%</TD></TR><TR><TD>3</TD><TD>0.63</TD><TD>19%</TD></TR><TR><TD>4</TD><TD>0.6</TD><TD>24%</TD></TR><TR><TD>5</TD><TD>0.59</TD><TD>20%</TD></TR><TR><TD>6</TD><TD>0.33</TD><TD>4%</TD></TR><TR><TD>7</TD><TD>0.14</TD><TD>8%</TD></TR><TR><TD>8</TD><TD>0.11</TD><TD>11%</TD></TR><TR><TD>9</TD><TD>0</TD><TD>5%</TD></TR><TR><TD>10</TD><TD>0</TD><TD>2%</TD></TR></TBODY></TABLE><P> </P><P>Weighted Average (value im looking for) = 0.46</P><P>Average = 0.37</P><P> </P><P>Bonus if it can be dynamic (e.g. user filters for identifier 1,2,3 and caculates weight and then weighted mean)</P>Fri, 16 Apr 2021 05:09:36 GMThttps://community.powerbi.com/t5/Desktop/Equivalent-of-SUMPRODUCT/m-p/1788318#M698745Rewind2021-04-16T05:09:36ZRe: Equivalent of =SUMPRODUCT?
https://community.powerbi.com/t5/Desktop/Equivalent-of-SUMPRODUCT/m-p/1788344#M698747
<P>Hi <LI-USER uid="85489"></LI-USER> </P>
<P>A measure like this should do the trick, assuming the weighting is to be done row-by-row over your table:</P>
<LI-CODE lang="markup">Weighted Average =
VAR WeightedSum =
SUMX (
YourTable,
YourTable[Scores] * YourTable[Weight]
)
VAR TotalWeight =
SUM ( YourTable[Weight] )
RETURN
DIVIDE ( WeightedSum, TotalWeight )</LI-CODE>
<P>The measure iterates over your table, summing Scores * Weight, then divides by the total Weight.</P>
<P>This will also be dynamic in that it will respond to any filters you apply.</P>
<P> </P>
<P>Regards,</P>
<P>Owen</P>Fri, 16 Apr 2021 05:33:26 GMThttps://community.powerbi.com/t5/Desktop/Equivalent-of-SUMPRODUCT/m-p/1788344#M698747OwenAuger2021-04-16T05:33:26ZRe: Equivalent of =SUMPRODUCT?
https://community.powerbi.com/t5/Desktop/Equivalent-of-SUMPRODUCT/m-p/1788410#M698766
<P>Thanks Owen, this solved my problem. Should have come here a few hours ago <span class="lia-unicode-emoji" title=":grinning_face_with_sweat:">ðŸ˜…</span></P>Fri, 16 Apr 2021 06:18:53 GMThttps://community.powerbi.com/t5/Desktop/Equivalent-of-SUMPRODUCT/m-p/1788410#M698766Rewind2021-04-16T06:18:53Z