"Average" is close to useless. Need to be able to automatically only average non-zeros

The average summary method and aggregation function [Average:] both include zero values when calculating an average.


This makes sense but sadly means that in almost all scenarios the irritating workaround of counting non-zeros and then dividing is required. This costs line items (and therefore space), increases calculation complexity and takes time. 


Hopefully, there is a way of augmenting the current average function because where every line item is precious, counting non-zeros is more than irritating, it is not possible.

11 votes

New · Last Updated


  • This doesn't help when using the summary method, or using the AVERAGE() formula, but if you are using the aggregation function [AVERAGE: <helper>], you can get around this by putting the logic to exclude 0s in the helper.


    E.g. in your <helper>, IF measure <> 0 THEN <helper> ELSE BLANK 


    The AVERAGE aggregation will then only include your non-zero amounts. 

  • Definitely think we should have the ability to apply an "IF" logic to all of the aggregation functions to exclude zero values from the input data set. So we can get a true Average or Min or Max number.

Get Started with Idea Exchange

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