Nested Aggregation - Sum of Averages

Question and answer
#1

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

image
image903×211 5.54 KB

#2

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.

#3

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.

image
image754×280 9.44 KB

#4

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]).

image

1 Like