Table of Contents
The AVERAGE IF function is a powerful tool in Power BI that allows users to calculate the average of a specific set of data based on a given criteria. This function is useful for analyzing large datasets and gaining insights based on specific conditions. To use the AVERAGE IF function in Power BI, simply specify the range of data and the criteria for which you want to calculate the average. For example, you can calculate the average sales for a particular product or region. This function is a valuable tool for data analysis and can be easily implemented in Power BI.
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:
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 .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: