Deep Dive into DAX AVERAGE Function

The AVERAGE function in DAX (Data Analysis Expressions) is a mathematical function that calculates the arithmetic mean of a column of numbers. It’s a fundamental function when dealing with data that involves statistical analysis, such as calculating average sales, average customer ratings, or average performance metrics. In this guide, we’ll take a deep dive into the AVERAGE function, exploring its syntax, usage, and some advanced scenarios.

Syntax

The syntax of the AVERAGE function is as follows:

AVERAGE(<columnName>)

• <columnName>: The name of the column that contains the numbers you want to average.

The function returns the arithmetic mean of all the numbers in the column.

Basic Usage

Let’s start with a basic example. Suppose you have a sales table with a column for the sales amount of each transaction, and you want to calculate the average sales amount.

Here’s how you can do it with the AVERAGE function:

Average Sales = AVERAGE(Sales[Sales Amount])

This formula creates a new measure called “Average Sales” that calculates the average sales amount in the Sales table.

Advanced Usage

The AVERAGE function can also be used in more advanced scenarios. For example, you can use it to calculate the average sales per customer or to calculate the average sales per day.

Here’s an example of calculating the average sales per customer:

Average Sales per Customer = AVERAGEX(VALUES(Sales[Customer ID]), [Total Sales])

This formula calculates the total sales for each customer and then calculates the average of these values. The result is the average sales per customer.

And here’s an example of calculating the average sales per day:

Average Sales per Day = AVERAGEX(VALUES(Sales[Date]), [Total Sales])

This formula calculates the total sales for each day and then calculates the average of these values. The result is the average sales per day.

Conclusion

The AVERAGE function in DAX is a powerful tool for calculating the arithmetic mean of a column of numbers. By understanding and effectively using this function, you can perform statistical analysis and gain deeper insights into your data. So, keep exploring the capabilities of the AVERAGE function, and take your Power BI skills to the next level!