How can the average be calculated in Power BI using an example?

Understanding the DAX AVERAGE Function Syntax

To define custom metrics and perform dynamic aggregations within Power BI, we utilize DAX (Data Analysis Expressions). The AVERAGE function is a fundamental aggregate function within DAX, designed to calculate the arithmetic mean. When implementing this function, we typically create a new Measure, ensuring the resulting calculation dynamically adapts to user interactions and filters applied across the report canvas.

The standard syntax for creating a measure requires naming the measure, followed by the AVERAGE function, which must explicitly reference the targeted column and table name. This structure guarantees that the calculation engine correctly identifies and processes the intended numerical field.

You can use the following syntax in DAX to calculate the average value in a column:

Avg Points = AVERAGE('my_data'[Points])

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. This metric will automatically re-evaluate whenever the filter context changes in the report.

Practical Demonstration: Calculating Average Player Points

The following detailed walkthrough provides a concrete example of how to implement and visualize this average calculation in practice. We will use a representative dataset covering sports statistics to illustrate the process of creating the measure and integrating it into a report dashboard.

For this scenario, assume we have imported data concerning basketball players, specifically tracking their performance points. This data resides in a table within Power BI named my_data. Our objective is to calculate the overall average points scored across all entries in this table, focusing specifically on the Points column.

The following example shows how to calculate the average value of a column in Power BI in practice.

Example: How to Calculate Average 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:

Suppose we would like to calculate the average value in the Points column.

Step-by-Step Measure Creation

The first procedural step involves initiating the measure creation tool within Power BI Desktop. This process ensures that the calculated average is stored as a reusable and dynamic metric within the data model, which is crucial for interactive reporting.

To do so, click the Table tools tab along the top ribbon, then click the New measure icon:

Once the formula bar is visible, we input the precise DAX formula defined earlier. This command instructs Power BI to calculate the arithmetic mean of all values present in the specified data column, thus defining the Avg Points metric.

Then type in the following formula into the formula bar:

Avg Points = AVERAGE('my_data'[Points]) 

Executing this formula successfully registers the new measure in the field list, ready for deployment in any visualization. It is important to note that this measure is calculated using the entire dataset by default, providing the grand average across all data points.

This will create a new measure named Avg Points that contains the average of values in the Points column of the table:

Visualizing the Calculated Average using a Card

To make the newly calculated average accessible and prominent in the report, we utilize a Card visual, which is ideally suited for displaying single, summarized numeric values. Transitioning to the Report View is necessary for this step.

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:

Once the Avg Points measure is assigned to the Card visual, the aggregated result will immediately be displayed on the report canvas. This visual confirmation provides instant insight into the overall performance metric derived from the source data.

This will produce the following card that displays the average value in the Points column of the table:

We can see that the average value in the Points column is 19.91. This figure represents the grand mean points scored across all players in the dataset.

Exploring Dynamic Filter Context

A significant advantage of using DAX measures is their inherent ability to recalculate based on the filter context applied in the report view. If the report incorporates interactive elements, such as a table or slicer, the measure will automatically update to reflect only the filtered subset of data.

To illustrate, imagine the report contains a table visual listing player details, including their team affiliation. If a user selects a specific team name in that table, the filter context changes, and the Avg Points card instantaneously updates its calculation to reflect the average points for that single, selected team.

If we click on any of the individual names in the Team column of the table in the report, then the card will automatically update to show the average points value for that particular team.

For example, if we initiate a filter by clicking the Mavs team name in the accompanying data table, the measure re-evaluates its calculation, focusing only on the players belonging to the Mavs organization.

For example, suppose we click the Mavs team name in the table:

The card automatically updates to show that the average of values in the Points column for the Mavs is 18.75. This dynamic responsiveness is key to conducting granular, interactive analysis in Power BI.

Summary of Key Takeaways and Further Learning

The foundation of effective data analysis in Power BI relies on defining accurate and dynamic measures using DAX. The AVERAGE function provides the simplest way to calculate the arithmetic mean, serving as a critical starting point for metric definition. By following the measure creation process, users ensure that their calculated averages are reliable and adapt smoothly to user interactions.

This foundational understanding of the AVERAGE function paves the way for mastering more complex iterative average functions, such as AVERAGEX, which calculates the average of an expression evaluated over a table. Continued exploration of DAX syntax will unlock the full potential of Power BI for sophisticated business intelligence reporting.

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