Calculating the average values for a set of grouped data is a common task when analyzing data. It’s a frequent occurrence that requires attention and precision. For instance, you may want to determine the average sales per product category, average order size per customer, or average student score per grade level.

Working with Power BI, you can effortlessly calculate the average per group, thanks to its intuitive and user-friendly environment. In this article, you will learn the methods and tools available within Power BI to help you calculate average values for each group efficiently.

Calculate the Average Per Group from Visualization Options

Power BI offers a set of fundamental predefined calculations such as sum, average, count, minimum, and maximum. These calculations are typically accessible in visuals with a value well, making it the most straightforward method to determine averages.

By leveraging this approach, you can easily compute average values with ease and efficiency.

Using this method for returning the average per group is straightforward. Simply add the column with the desired values into the Values well of your visual, then click the dropdown arrow that displays on the edge.

From the contextual menu that appears, select Average to return the desired result.

Note that you can only use this option for columns with numeric values. Also, the option to use a predefined calculation is not available for measures.

Calculate Average Per Group with DAX Measures

Measures provide the flexibility to calculate various metrics, the least of which includes calculating the average per group for a dataset. This capability stems from measures evaluating based on the current filter context and the ability to customize the filter contexts within which they evaluate.

For this approach to calculating the average per group, you can use one or more DAX measures.

Calculate the Arithmetic Average Per Group with AVERAGEX

The AVERAGEX is an iterator DAX function that calculates the arithmetic mean of an expression. The AVERAGEX syntax is as follows.

AVERAGEX ( <Table>, <Expression> )

• <Table> – this is the table containing the rows over which the <Expression> will evaluate.
• <Expression> – the DAX expression the AVERAGEX will evaluate for each row in the <Table>.

AVERAGE = AVERAGEX ( FactInternetSales, [Total Sales] )

With this syntax, you can easily calculate the average sales for each category. To make the AVERAGEX simpler to use, first create a measure that returns the aggregate of the metric you want to average. Then add the measure as the <Expression> argument in the AVERAGEX function as it is done here.

In this syntax, the [Total Sales] measure returns the overall sales value. When used in the table, the AVERAGEX function evaluates the [Total Sales] measure for each category and returns their arithmetic mean.

Essentially, this means the total sales for each category are divided by the number of times it appears in the table.

AVERAGE_2 = [Total Sales] / COUNT ( FactInternetSales[EnglishProductName] )

AVERAGE (DIVIDE) = 
DIVIDE(
    [Total Sales],
    COUNT (
        FactInternetSales[EnglishProductName] 
    )
)

These are different ways you can write the AVERAGEX syntax so that they produce the same result.

You can also use the AVERAGE function.

AVERAGE ( <ColumnName> )

Unlike the AVERAGEX function, however, you can’t evaluate an expression with the AVERAGE function as it takes only a column name as an argument. It essentially is the DAX equivalent of using the predefined functions available when you calculate using visualizations.

Calculate Rolling Average Per Group with Time Intelligence Functions

You can enhance your calculation of the arithmetic average of grouped data by taking it a step further: computing the rolling average over time.

Rolling averages offer a holistic narrative, unraveling the underlying story behind the data with precision and clarity. It introduces a dynamic element that adds depth to the interpretation of data.

This also allows for a more comprehensive and robust understanding of how a specific metric evolves over time, surpassing the simplistic insights provided by arithmetic averages.

To calculate the rolling average, you use DAX’s powerful time intelligence functions. Understanding and using these functions are quite straightforward, particularly when you grasp the basics of filter and row context.

To get a comprehensive breakdown of calculating rolling averages, check out this article. Check here to understand the basics of row and filter context.

📒📃 Read More: Check out this article on calculating rolling averages, or this article on the basics of row and filter context.

Conclusions

Being able to calculate and visualize average values within specific groups is a powerful way to observe patterns and gain valuable insights. As a data analyst, this skill is fundamental and will be utilized frequently.

Thankfully, Power BI offers a range of options to calculate averages from your data. This enables you to unleash the complete potential of your datasets, allowing you and other users to obtain a comprehensive understanding of key metrics and make informed decisions.