Forum Discussion
Weighted average score
***EDITED to include more information
Hello community 🙂
I am currently working with some survey data to create a PowerBI report. The data is weighted by several demographic variables. I would like to calculate mean scores, however not sure how to do this to create a report that is reflective of the weighting. Dummy data below.
The output I would like is a simple average score:
Average of Score1 = X
Average of Score2 = X
Average of Score3= X.....
But I would like to be able to filter it by Gender, Industry and Age group
| ID | Weight | Gender | Industry | Age group | Score1 | Score2 | Score3 | Score4 | Overall score |
| 1 | 0.769148582 | Male | Health | 18 - 24 | 68.8 | 68.8 | 59.4 | 66.3 | 65.8 |
| 2 | 1.646973984 | Male | Information Technology | 18 - 24 | 100.0 | 96.9 | 64.3 | 67.4 | 82.2 |
| 3 | 0.908653866 | Male | Transport | 18 - 24 | 59.4 | 59.4 | 75.0 | 64.8 | 64.6 |
| 4 | 1.179551736 | Male | Finance | 18 - 24 | 75.0 | 81.3 | 78.1 | 82.5 | 79.2 |
| 5 | 1.539003936 | Male | Health | 25 - 34 | 50.0 | 68.8 | 50.0 | 55.4 | 56.0 |
| 6 | 0.797782928 | Male | Information Technology | 25 - 34 | 59.4 | 75.0 | 62.5 | 55.6 | 63.1 |
| 7 | 0.860841122 | Male | Transport | 25 - 34 | 75.0 | 62.5 | 60.7 | 63.6 | 65.5 |
| 8 | 0.688758334 | Male | Finance | 25 - 34 | 28.1 | 32.1 | 71.9 | 63.8 | 49.0 |
| 9 | 0.987520537 | Male | Health | 35 - 44 | 93.8 | 100.0 | 68.8 | 75.6 | 84.5 |
| 10 | 0.569148582 | Male | Information Technology | 35 - 44 | 68.8 | 75.0 | 62.5 | 53.1 | 64.8 |
| 11 | 4.000000000 | Male | Transport | 35 - 44 | 78.1 | 81.3 | 50.0 | 39.4 | 62.2 |
| 12 | 0.653696256 | Male | Finance | 35 - 44 | 62.5 | 75.0 | 68.8 | 75.0 | 70.3 |
| 13 | 1.631421649 | Female | Health | 18 - 24 | 71.9 | 65.6 | 65.6 | 60.0 | 65.8 |
| 14 | 0.681652090 | Female | Information Technology | 18 - 24 | 68.8 | 50.0 | 53.1 | 58.8 | 57.7 |
| 15 | 1.321545102 | Female | Transport | 18 - 24 | 81.3 | 87.5 | 81.3 | 71.9 | 80.5 |
| 16 | 0.582127359 | Female | Finance | 18 - 24 | 50.0 | 75.0 | 81.3 | 90.6 | 74.2 |
| 17 | 1.360796946 | Female | Health | 25 - 34 | 34.4 | 87.5 | 50.0 | 50.0 | 55.5 |
| 18 | 1.287520537 | Female | Information Technology | 25 - 34 | 71.9 | 71.9 | 50.0 | 50.0 | 60.9 |
| 19 | 0.669148582 | Female | Transport | 25 - 34 | 46.9 | 64.3 | 78.1 | 81.3 | 67.6 |
| 20 | 2.804766145 | Female | Finance | 25 - 34 | 81.3 | 65.6 | 75.0 | 75.0 | 74.2 |
| 21 | 0.830279756 | Female | Health | 35 - 44 | 50.0 | 50.0 | 50.0 | 50.0 | 50.0 |
| 22 | 1.681729157 | Female | Information Technology | 35 - 44 | 62.5 | 65.6 | 53.1 | 39.3 | 55.1 |
| 23 | 0.747406699 | Female | Transport | 35 - 44 | 50.0 | 62.5 | 68.8 | 71.9 | 63.3 |
| 24 | 1.800526115 | Female | Finance | 35 - 44 | 46.9 | 46.9 | 87.5 | 71.9 | 63.3 |
Thank you,
Mel
4 Replies
This is a standard pattern - find the sum of all the variables multiplied by their weight, then divide by the sum of the weights.
Thank you, that's helpful!
However I also need to filter by multiple other categorical variables (e.g. gender, industry) and this doesn't seem to be coming up with accurate mean scores, because the current calculation doesn't take into account the weighted counts
