---

You can use the following syntax in DAX to calculate the average value in a column while ignoring any values equal to zero:

Avg Points = 
CALCULATE (
    AVERAGE ( 'my_data'[Points] ),
    FILTER ( 'my_data', 'my_data'[Points] <> 0 )
)

This particular example creates a new measure named Avg Points that calculates the average value in the Points column of the table named my_data while ignoring any values equal to zero.

The following example shows how to calculate the average value of a column in Power BI in practice.

Example: How to Calculate Average and Ignore Zeros in Power BI

Suppose we have the following table in Power BI named my_data that contains information about points scored by basketball players on various teams:

Notice that there are several values equal to zero in the Points column.

Suppose we would like to calculate the average value in the Points column while ignoring these zero values.

To do so, click the Table tools tab along the top ribbon, then click the New measure icon:

Then type in the following formula into the formula bar:

Avg Points = 
CALCULATE (
    AVERAGE ( 'my_data'[Points] ),
    FILTER ( 'my_data', 'my_data'[Points] <> 0 )
)

This will create a new measure named Avg Points that contains the average of values in the Points column of the table while ignoring the zeros:

If we’d like, we can display this value by going to the Report View in Power BI, then by clicking the Card icon under the Visualizations tab, then by dragging the Avg Points measure under the Fields label:

We can see that the average value in the Points column, ignoring all zeros, is 17.5.

We can verify this is correct by manually calculating the average of the values in the Points column while ignoring all zeros:

Average of Points while Ignoring Zeros: (22+19+15+20+21+15+18+10) / 8 = 17.5

This matches the value calculated by our formula.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI: