Weighted Sum

Question

Hi, I have a large data table, call it Results, that has attributes for many "parts". I have a set of measures (call them A, B, C) that compare three systems, say X, Y nd Z in which the parts are used. I have a matrix visual that is constructed as parts x measures (parts in rows, measures in values). I also have a separate "Weights" table that has weights for each of the measure A, B and C. I want to compute a weighted sum for each part, calculated as ((Measure A * Weights A + Measure B * Weights B + Measure C * Weights C) / Sum of all weights) for systems X and Y. It is easy to do in Excel, but I am not finding an easy solution for Power BI. I'd appreciate any help from anyone here.The matrix visual lookes like this:&nbsp;Parts&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Measure A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Measure B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Measure C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Weighted Suma&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.93&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.42&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ?b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.37&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.51&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ?The weighted sum column is what I would like to add.Thanks

anonymous · Answer

Hi neskowin&nbsp;,
&nbsp;
The information you provided is rather vague, perhaps you could provide me with the pbix file?
&nbsp;
Please be careful not to log into your account in Power BI Dektop and not to include sensitive information when uploading the pbix file.
&nbsp;
Best Regards,YangCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly.If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!

neskowin · Answer

Thank you for the prompt response. I am not able to attach my PBIX file as it is huge. Instead, I constructed an Excel sheet with sample data that illustrate my PBIX file and the desired outcome. I hope the problem description is clear. I am not able to attach the .xlsx file to this message. Hence, I have tried to copy its content below. Thank you.

Matrix Visual (the Value columns are measures). The measures are to be referenced in the Weight Sum.

Parts	Sytem X vs. System Y	System X vs. System Z	System Z vs. System XX	Weighted Sum
PartA	0.976	0.925	1.031	?
PartB	0.968	0.900	1.042	?
PartC	0.971	0.910	1.037	?
PartD	0.963	0.886	1.048	?
PartE	0.963	0.888	1.047	?

Weights Table

Measure Name	Weight
Sytem X vs. System Y	5.2
System X vs. System Z	-7.1
System Z vs. System XX	8.9

The "Weighted Sum" in the Matrix Visual should be as follows:
(Sytem X vs. System Y * 5.2 + System X vs. System Z * -7.1 + System Z vs. System XX * 8.9) / SUM of all weights)
The numbers used as multipliers are from the corresponding rows in the Weights Table.

Results Table

Systems	Parts	Attribute1
System X	PartA	123.1
System X	PartB	89.7
System X	PartC	101.3
System X	PartD	77.5
System X	PartE	79.1
System Y	PartA	126.1
System Y	PartB	92.7
System Y	PartC	104.3
System Y	PartD	80.5
System Y	PartE	82.1
System XX	PartA	129.1
System XX	PartB	95.7
System XX	PartC	107.3
System XX	PartD	83.5
System XX	PartE	85.1
System Z	PartA	133.1
System Z	PartB	99.7
System Z	PartC	111.3
System Z	PartD	87.5
System Z	PartE	89.1

Forum Discussion

Weighted Sum

2 Replies

Related content

Calculating Weighted Sales

Calculating Weighted Average

Running sum

Weighted SUM with Isinscope

Weighted Average shows wrong sum