Ability to exclude zero values from AVERAGE calculation


A common requirement we see on customer implementations is to be able to calculate the average of a set of values but exclude zero values.  For example if we have the following set of values (10,0,0,8,0), the average excluding zeros would be (10+8)/2 = 9.  


The current AVERAGE function cannot exclude zeroes and so gives an AVERAGE of 18/5=0.2 in this case.


A common workaround then is to add another line item to count non zero values (IF Data <> 0 THEN 1 ELSE 0) and use this to separately calculate the non zero average.


It would be useful to have an extra parameter to the AVERAGE function which would allow us to include or exclude zeros from the calculation and avoid this extra modelling complexity.


76 votes

In Review · Last Updated


  • jon

    I agree with this as well. While it's fine to create the extra line item, I've found that I'm excluding zeros most of the time so the current workaround seems a bit unnecessary. 

  • Yes, PLEASE.

  • Status changed to: In Review
  • Status changed to: In Review
  • Indeed, I never used the "AVERAGE" function or summary method exactly for this reason: not having the possibility to exclude the zeros. 

    In a multidimensional database where not all the cells are populated, there are very few cases where ALL the cells from the intersections of all the dimensions from a module should contribute to the AVERAGE of the upper levels. 


    This is why I only used the workaround. 


    This additional parameter to have the possibility to exclude zeros (or blank) could be also very useful for MIN function/summary method. 


  • Agree with @alexpavel 

    In addition to AVERAGE, we need MIN, MAX and MEDIAN with a parameter that allows us to exclude zeros, NAN, and INFINITY.

  • cschoeb

    great idea!

Get Started with Idea Exchange

See our Submission Guidelines and Idea Evaluation Criteria, then start posting your own ideas and showing support for others!