Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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...
Identifier | Scores | Weight |
1 | 0.67 | 4% |
2 | 0.67 | 4% |
3 | 0.63 | 19% |
4 | 0.6 | 24% |
5 | 0.59 | 20% |
6 | 0.33 | 4% |
7 | 0.14 | 8% |
8 | 0.11 | 11% |
9 | 0 | 5% |
10 | 0 | 2% |
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)
Solved! Go to Solution.
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
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
Thanks Owen, this solved my problem. Should have come here a few hours ago 😅
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |