DAX Top 5 Customers by Average Order Value

This code snippet is written in DAX (Data Analysis Expressions), which is a formula language used commonly in Power BI, Analysis Services, and Power Pivot in Excel.

Code Breakdown:

• TOPN: This is a DAX function that returns the top 'N' rows of a table or column. In this case, it's set to return the top '5'.
• VALUES(Sales[CustomerName]): VALUES function returns a one-column table that contains the distinct values from the specified column, in this case 'CustomerName'.
• CALCULATE(AVERAGEX(Sales, Sales[OrderValue])): Here, the CALCULATE function changes the context in which the data is analyzed, and the AVERAGEX function averages the 'OrderValue' for each 'CustomerName'.

The final DESC keyword sorts the results in descending order of the calculated average Order Value, hence giving you the top 5 customers with the highest average order value.

Use of CALCULATE function

The CALCULATE function is one of the most crucial and powerful functions in DAX. It allows you to modify the context under which an expression is evaluated. It means it can change the Filter Context or the set of values considered while performing calculations.

In this context, we're using CALCULATE to evaluate the average Order Value within the set of unique customers. So for each unique customer (established by VALUES function), CALCULATE helps to change the context and calculate the average order value.

For instance, suppose, without CALCULATE, the AVERAGEX function would consider all the 'OrderValues' in 'Sales' to calculate the average disregarding the unique 'CustomerName'. CALCULATE forces it to evaluate AVERAGEX within the context of each unique 'CustomerName'.

For an in-depth understanding of these DAX functions, Enterprise DNA platform provides a detailed course on DAX formulas which is recommended.

Top5Customers = 
TOPN (
    5, 
    VALUES ( Sales[CustomerName] ), 
    CALCULATE ( AVERAGEX ( Sales, Sales[OrderValue] ) ),
    DESC 
)