How do I create an average that will exclude zeros?

How can I do an average on numbers and exclude the zeros? For example, Year 1 = 10, Year 2 = 0, Year 3 = 4, therefore if ignore Year 2, average is 7 (and not 14 divided by 3)?

Accepted answers

Mark_W_Shemaria

You will need to have an additional line item(s) which "filter" out the zeros and count the non zeros. Anaplan's average function will not natively exclude zeros in calculating the sum of items / number of items in the list(s).

All comments

Mark_W_Shemaria

tbeck

1) Create a "Count" Line Item formatted as a Number

2) Formula = IF (line item) = 0 then 1 else 0 --> this will make a count for the denominator for the average.

3) Create a line item for the calculation, here the Ratio summary can be used or the Sum of Values / Count

Quick Links

Can you help?

Unanswered questions

Is there a way to turn off the Show Comments Summary panel in UX pages with Comments?
This shows all comments across all data to all users with access to the UX page. This is not appropriate for any UX which might contain security access sensitive data. Comments on grids and cards ARE security dependent. That works fine. But anyone with access to the page can open this Summary Panel and see ALL comments…
Are there any plans to support actions to rename or delete native Versions?
If anyone is aware of idea exchange ideas that are currently being evaluated by the product team, if you don't mind linking them here, would love to give them an upvote. Thanks!
ADO Module Saved View does not exist Error
Hi, I’m experiencing a strange issue in ADO that I can’t seem to resolve. Before reaching out to Anaplan Support, I thought I would check with the community for advice. I’m trying to extract some list properties for an ADO mapping from a Polaris model/module. However, no matter what I do, I keep encountering the same error…