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:

This will produce the following card that displays the average value in the **Points** column of the table while ignoring all zeros:

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:

How to Count Number of Occurrences in Power BI

How to Calculate Standard Deviation in Power BI

How to Calculate a Cumulative Sum in Power BI