Aggregating stratified data

[nnn123]

Hi,

I'm trying to aggregate some survey results in Power BI. The survey respondents were all split into 4 categories/strata and then a weight applied to the separate stratum before calculating weighted results, as in table below (Weighted Result = Result * Weight)

Council	Result	Stratum	Weight	Weighted Result
1	10	1	10.5	105
1	40	2	12	480
1	60	3	15.2	912
1	20	4	9.5	190
2	15	1	8	120
2	20	2	10.8	216
2	50	3	11	550
2	60	4	13.5	810
3	30	1	13	390
3	30	2	10.5	315
3	50	3	12	600
3	60	4	15.2	912

I don't want to display the results at strata level in Power BI report though so I would like to be able to calculate the weighted results by strata and then display council level aggregated results only like this:

Council	Weighted Result
1	1687
2	1696
3	2217

In this table above all the 4 separate strata results have been aggregated together for each council. When I try this in a Power BI 'Table' visualisation though it just sums everything (including adding all the weight column together) which is not what i want - the 4 separate weighted results must be calculated individually first in order to produce correct results. Is it possible to produce this simple 2-column table above in a Power BI report?

[jthomson · Accepted solution]

Make a measure WeightedResult = Calculate(sumx(tablename,tablename[Result]*tablename[weight]))