Calculate the overall/aggregated weighted average for a column based on individual weighted averages

[tomekm]

Hello,

I created a calculated column that computes the weighted average for each row ID in my dataset, and when I try to show the overall/aggregated weighted average at the bottom of the column, pbi can only show "SUM" of those weighted averages, and when changing the option to AVG, it shows the average of those weighted averages, instead of the aggregated weighted average. For the dataset below, for column B it should show a value of 11 as being the overall weighted average, instead of the 9.5 which is the aritmethic average of the computed weighted averages. Any ideas on how to fix this so that I can show the overall/aggregated weighted average?

item ID	calculated column for weighted average:
1	10
2	14
3	9
4	5
total	38
avg	9.5

[lbendlin · Accepted solution]

Instead of using a calculated column for the weighted average, use a measure that fully implements your desired business logic, both for the individual "rows"  and for the row totals.