Power BI: How to Calculate Average and Ignore Zeros


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:

Power BI calculate average and ignore 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

Leave a Reply

Your email address will not be published. Required fields are marked *