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.

 

4 Comments
Certified Master Anaplanner jon Certified Master Anaplanner
Certified Master Anaplanner

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. 

Certified Master Anaplanner

Yes, PLEASE.

Community Manager
 
Status changed to: Needs Community Support
Community Manager
 
Status changed to: Under Investigation
Users Online
Currently online: 201 members 379 guests
Please welcome our newest community members: