2 Replies Latest reply on Jan 11, 2019 2:26 AM by to.van

    Calculate average with Null values calculated as Zero including a Hierarchy




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


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



      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