A Power BI model includes a 'Customers' table and an 'Orders' table. The 'Customers' table contains 'CustomerID' and 'CustomerSegment' columns. The 'Orders' table contains 'OrderID', 'CustomerID', and 'OrderValue' columns. There is a one-to-many relationship from 'Customers[CustomerID]' to 'Orders[CustomerID]'. The data analyst needs to calculate the average order value for each customer segment. Which DAX function should be used to achieve this efficiently?
- AAVERAGEX(Customers, CALCULATE(AVERAGE(Orders[OrderValue])))
- BAVERAGE(Orders[OrderValue])
- CAVERAGEX(Orders, Orders[OrderValue])
- DAVERAGEX(Customers, SUMX(RELATEDTABLE(Orders), Orders[OrderValue]))
Show answer & explanationAnswer & explanation
Correct answer: D. AVERAGEX(Customers, SUMX(RELATEDTABLE(Orders), Orders[OrderValue]))
To calculate the average order value *per customer segment*, we need to iterate over each customer in the 'Customers' table (which implicitly groups by segment when placed in a visual). For each customer, we then need to sum their individual order values. 'AVERAGEX(Customers, ...)' iterates through each customer. 'SUMX(RELATEDTABLE(Orders), Orders[OrderValue])' then calculates the total order value for that specific customer by iterating over their related orders. Finally, AVERAGEX averages these sums per customer, resulting in the desired average order value per segment.
Why the other options are wrong
- A. This expression uses AVERAGEX on 'Customers' but then attempts to average 'OrderValue' directly within CALCULATE, which would average all orders visible to the current customer context, not the sum of orders per customer.
- B. This would calculate the average of all order values in the current filter context, not correctly aggregating per customer then averaging per segment.
- C. This would calculate the average of all order values, iterating row by row in the 'Orders' table, not performing the necessary aggregation per customer before averaging.
X-Functions (Iterators)
X-functions (e.g., SUMX, AVERAGEX, COUNTX) are iterator functions in DAX that evaluate an expression for each row of a specified table, then perform an aggregation. They are crucial for row-context calculations.
- Evaluate an expression row by row.
- Create a row context for each iteration.
- Can be used to perform calculations that depend on individual row values.
- Often used with RELATED or RELATEDTABLE to cross relationships in row context.
Memory trick: X-functions iterate rows, then calculate; AVERAGEX over SUMX for average of sums.