SELECT
AVG (CASE WHEN Value <> 0 THEN Value ELSE NULL END)
....
AVG function will not take into account NULL values.
So you can also use this
AVG (NULLIF(Value, 0))
Related posts:
How to find the first missing value in a series in MS SQL
Unique Constraints in MS SQL Server to prevent duplications
Capitalize words in SQL (Display data first letter uppercase rest lowercase)
Drag and Drop objects from Object Explorer to the Query Window in SQL Server
Replace duplicate spaces with a single space in SQL