Weighted average score

[mel189]

***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

[mel189]

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

[mel189]

Thanks, will do!

[lbendlin]

Please provide sanitized sample data that fully covers your issue.

Please show the expected outcome based on the sample data you provided.

[lbendlin]

This is a standard pattern -  find the sum of all the variables multiplied by their weight, then divide by the sum of the weights.