I am looking for a way to calculate the weighted average (based on the nr of people for a given score) in a scorecard for a given data range. See sample data and sample report below.
Sample data
https://docs.google.com/spreadsheets/d/1z3C4SOitKz7gJw80km3b8U3_YRtyPclL54d_teZvrVY/edit?usp=sharing
Sample report
https://lookerstudio.google.com/reporting/f08a5c6a-76cd-431b-9221-25513bf49ef4
Conceptually I would like to create a calculated field SUM(score x people count) / total people count.
What I tried so far ...
First select a data range that has some result. Based on the data I can get the normal average. I can also calculate the relative contribution to the whole based on people count. Looker is aware of the sum of all people count in the selected data range, I however cannot seem to use such a dynamic sum in a formula.
Any suggestions would be much appreciated.
Cheers, Alard
LinkedIn
Twitter
