Weighted Sum

[neskowin]

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: 
Parts         Measure A           Measure B              Measure C              Weighted Sum
a               0.93                     1.42                         1.01                          ?
b               1.37                     0.51                         1.20                          ?

The weighted sum column is what I would like to add.

Thanks

[Anonymous]

Hi @neskowin ,

The information you provided is rather vague, perhaps you could provide me with the pbix file?

Please be careful not to log into your account in Power BI Dektop and not to include sensitive information when uploading the pbix file.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  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. Thanks a lot!

[neskowin]

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