Calculating Average Excluding Zero Rows

[Alteryxexpert]

I have Data like below, for which i need to calculate average.

Name	Year	Value
Sales	2023	0
Sales	2023	10
Sales	2023	0
Sales	2023	15
Sales	2024	0
Sales	2024	150
Sales	2024	0
Sales	2024	300
Sales	2025	50
Sales	2025	0
Sales	2025	0
Sales	2025	100

For Example: If I want to calculate AVG for 2024 the calculation should only pick (150+300)/2=225 not (150+300+0+0)/4=112.5, Is there a way to achieve this for all the line items? 

[Alteryxexpert · Accepted solution]

Achieved Using summarize average Ignore 0's option

[gawa]

hi @Alteryxexpert 

To ignore the record in Summarize tool, value should be NULL. For example, replace the record which value is 0 with NULL, and go Summarize tool like this.

===========================================================

As a side note, this technique is also powerful when summarize string data. If some record are empty string and concatenate them, its result contains unwanted separator like

A,B,C,,E

However, by converting empty string to NULL, result will be like this format that should be preferable in most cases.

A,B,C,E