Hi

This is a followup to Re: Calculate average with Null values calculated as Zero, adding another difficulty.

I am trying to calculate averages of data, where some of the fields are Null, but should be treated as 0's in the average calculation. There is a nice solution posted in the above thread using this formula for the hours:

IF SIZE()<>1 THEN

  ZN(LOOKUP(AVG([Work]), 0))

ELSEIF SIZE()=1 THEN

  WINDOW_SUM(SUM([Work])) / MAX({COUNTD([Person])})

END

I would like to add two more caveats:

i) It should possible to have more than one datapoint per Person and Week, and they should be summed up into one entry per week per person.

ii) I would like to add a hierarchy for every person, i.e. Country -> Team -> Person

It should then be possible to get the correct averages on a Country, Team and Person level.

with the expected result:

Ideally, it should be possible to not show the individual lines, i.e. take the Person and Team pills out of the rows. Example workbook is attached.

Thanks a lot for the help, Tobias