Table of Contents
Power BI has a built-in function called AVERAGEX that allows for the calculation of an average while ignoring zero values. This function uses an iterative approach to calculate the average by first filtering out the zero values and then calculating the average based on the remaining values. This ensures that the zero values do not skew the overall average calculation. Additionally, Power BI also has the option to use the CALCULATE function, which allows for the creation of custom filters to ignore specific values, such as zero, in the average calculation. This functionality allows for more accurate and meaningful averages to be calculated in Power BI.
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: