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

tscott

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.

kevin.cho

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. 

nick_barker

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.