2 Replies Latest reply: Feb 20, 2017 2:47 PM by Benedikt Neumayer RSS

    Sum averages for one dimension

    Benedikt Neumayer

      Hi all,

       

      I am looking for the correct formula to create a sum of averages for a certain dimension in my datasheet.

       

      My datasheet is organized like this:

       

      CountryChannelAudience (individuals)
      AlbaniaChannel A

      1000

      AlbaniaChannel B2000
      BrazilChannel C3000
      BrazilChannel D4000

       

      I now need to create an average audience per country which needs to be summed up afterwards --> For this test case the result should say 5000.

       

      So far I have tried the following options, but none of them works:

       

      =sum(aggr(avg([Audience (Individuals)]), country))

       

      =sum(aggr(sum([Audience (Individuals)])/count([Audience (Individuals)]) , Country))

       

      In a second step it would be perfect if the formula could leave out those lines of the calculation, that do not have an entry for "Audience (individuals)".

       

      Any suggestions?

       

      Thanks a lot

       

      BN