Forum Discussion
Aggregating stratified data
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?
Make a measure WeightedResult = Calculate(sumx(tablename,tablename[Result]*tablename[weight]))
1 Reply
Make a measure WeightedResult = Calculate(sumx(tablename,tablename[Result]*tablename[weight]))