3 Replies Latest reply: Jun 30, 2015 2:06 AM by Amber Alo RSS

    Sum if value is greater than the average

    Amber Alo

      Hello,

       

      I would like to sum in chart only values that are greater than the average of this field.

      for example:

      Data

      ID Country Amount
      1France10
      2France16
      3France12
      7Greece32
      8Greece34
      9Greece38
      4Spain24
      5Spain22
      6Spain21

       

      I use this function: sum(aggr(if(sum(Amount)>avg(TOTAL <Country> amount),sum(Amount)),Country,ID))

       

      Chart:

      CountryAvg(Amount)What I want to getwhat I get
      France12.666667160
      Greece34.666667380
      Spain22.333333240

       

      Any suggestions?

       

      Thanks