Nested Aggregation - Sum of Averages

I’m trying to get 2 output columns from my Source Data below

  1. ‘Average Employee Score’ for every Employee
  2. A ‘Manager Score’ calculated as sum of the average employee scores (calculated in step above)

Have been trying to calculate sum of averages but Nested aggregations are not allowed. Does anyone have a solve

Hi @shankisaiyan, QS just launched the new Level-Aware Calculation capability. For you problem you can directly defind Mgr_Score= sum(average(employee_score, [emp_login])). Drag “Manager name” and “Mgr_Score” into the visual should give you the correct answer.

1 Like

Do you need the Employee Login field in your final visual? I think Emily’s solution will work if you remove that field from the visual. Maybe you will need two visuals side by side - one at the Employee level and another at the Manger level.

Hi @shankisaiyan, if you need to bring both mgr and emp in one table, you can use nested LAC-W : sumOver(avgOver(avg(target),[Employee]),[Manager]).


1 Like

hi @emilyzhu , may i know how can i display just manager with the final metric of sumover(avgover)?
I realised that if i do not put in employee in the group, there will be an error.