Forum Discussion
Context Transition in DAX
Hi Mederic,
Average Customer Sales = AVERAGEX(Customer, [Sales Amount]) iterates over the Customer table one row at a time. For each customer, DAX evaluates [Sales Amount]. Because [Sales Amount] is a measure, it has an implicit CALCULATE around it, which triggers context transition. The current customer row becomes a filter, so [Sales Amount] returns only that customer's sales. The Brand = Tailspin Toys filter from your pivot is still active, so DAX sums only Tailspin Toys sales for that one customer.
The 98.58 in the Tailspin Toys row equals the total Tailspin Toys sales divided by the number of customers who actually bought Tailspin Toys. AVERAGEX ignores blank iteration results, so customers with no Tailspin Toys sales are not counted in the denominator. A more descriptive name would be "Sales per Buying Customer" or "Average Sales per Customer".
Measure 2 works the same way but iterates Product instead of Customer, so 553.56 is the average sales per product that had sales in the current filter context. A better name is "Average Sales per Product".
On the quotes: DAX requires single quotes around a table name when the name contains spaces, starts with a digit, or matches a DAX reserved word or function name. Product collides with the PRODUCT function, so 'Product' has to be quoted. Customer has no such collision, so it can be written unquoted.
If this helped clarify things, please give it a kudos and mark it as the accepted solution.
Best regards,
Shai Karmani
Hi Mederic,
Average Customer Sales = AVERAGEX(Customer, [Sales Amount]) iterates over the Customer table one row at a time. For each customer, DAX evaluates [Sales Amount]. Because [Sales Amount] is a measure, it has an implicit CALCULATE around it, which triggers context transition. The current customer row becomes a filter, so [Sales Amount] returns only that customer's sales. The Brand = Tailspin Toys filter from your pivot is still active, so DAX sums only Tailspin Toys sales for that one customer.
The 98.58 in the Tailspin Toys row equals the total Tailspin Toys sales divided by the number of customers who actually bought Tailspin Toys. AVERAGEX ignores blank iteration results, so customers with no Tailspin Toys sales are not counted in the denominator. A more descriptive name would be "Sales per Buying Customer" or "Average Sales per Customer".
Measure 2 works the same way but iterates Product instead of Customer, so 553.56 is the average sales per product that had sales in the current filter context. A better name is "Average Sales per Product".
On the quotes: DAX requires single quotes around a table name when the name contains spaces, starts with a digit, or matches a DAX reserved word or function name. Product collides with the PRODUCT function, so 'Product' has to be quoted. Customer has no such collision, so it can be written unquoted.
If this helped clarify things, please give it a kudos and mark it as the accepted solution.
Best regards,
Shai Karmani