How to Use AVERAGE IF in Power BI (With Example)


You can use the following syntax in DAX to write an AVERAGE IF function in Power BI:

Avg Points =
CALCULATE (
    AVERAGE ( 'my_data'[Points] ),
    FILTER ( 'my_data', 'my_data'[Team] = EARLIER ( 'my_data'[Team] ) )
)

This particular formula creates a new column named Avg Points that contains the average value in the Points column for each unique value in the Team column in the table named my_data.

The following example shows how to use this formula in practice.

Example: How to Use AVERAGE IF in Power BI

Suppose we have the following table in Power BI named my_data that contains information about various basketball players:

Suppose we would like to create a new column that shows the average points scored by players on each team.

To do so, click the Table tools tab, then click the New column icon:

Then type the following formula into the formula bar:

Avg Points =
CALCULATE (
    AVERAGE ( 'my_data'[Points] ),
    FILTER ( 'my_data', 'my_data'[Team] = EARLIER ( 'my_data'[Team] ) )
)

A new column named Avg Points will be created that shows the average points value for each team:

Power BI AVERAGE IF

From the output we can see:

  • The average points value for players on the Mavs team is 18.75.
  • The average points value for players on the Rockets team is 24.
  • The average points value for players on the Spurs team is 21.67.
  • The average points value for players on the Hornets team is 15.5.

Note: You can find the complete documentation for the AVERAGE function in Power BI here.

Additional Resources

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

Power BI: How to Calculate Average Value in Column
Power BI: How to Calculate Average and Ignore Zeros
Power BI: How to Calculate Average By Month

Leave a Reply

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