Forum Discussion
Solved
about evaluation context
there is an example in Microsoft training modules 2-aggregation-iterator-functions . and in the higher grain summarization part, it gives a formula:
Revenue Avg Order =
AVERAGEX(
VALUES('Sales Order'[Sales Order]),
[Revenue]
)where [Revenue] is:
Revenue =
SUMX(
Sales,
Sales[Order Quantity] * Sales[Unit Price] * (1 - Sales[Unit Price Discount Pct])
)lets put them together:
Revenue Avg Order =
AVERAGEX(
VALUES('Sales Order'[Sales Order]),
SUMX(
Sales,
Sales[Order Quantity] * Sales[Unit Price] * (1 - Sales[Unit Price Discount Pct])
)
)my question is:
is the formula above the same as this one? why?
(I test these two formulas and find them return the same result in my data model. )
Revenue Avg Order2 =
AVERAGEX (
VALUES('Sales Order'[Sales Order]),
SUMX (
FILTER(Sales, Sales[Sales Order] = EARLIER('Sales Order'[Sales Order])),
Sales[Order Quantity] * Sales[Unit Price] * (1 - Sales[Unit Price Discount Pct])
)
)
chatgpt gives an answer as follows:
Execute SUMX
- For each Sales Order, AVERAGEX passes the current Sales Order as the row context and evaluates the SUMX function.
- SUMX(Sales, ...) iterates over the Sales table. However, because DAX applies the current row context automatically, the SUMX will only process rows in the Sales table that match the current Sales Order.
- This is due to DAX’s context propagation, where the outer row context (in this case, the current Sales Order) is passed into the inner calculation, so SUMX operates only on rows that match the Sales Order.
2 Replies
chatgpt gives an answer as follows:
Execute SUMX
- For each Sales Order, AVERAGEX passes the current Sales Order as the row context and evaluates the SUMX function.
- SUMX(Sales, ...) iterates over the Sales table. However, because DAX applies the current row context automatically, the SUMX will only process rows in the Sales table that match the current Sales Order.
- This is due to DAX’s context propagation, where the outer row context (in this case, the current Sales Order) is passed into the inner calculation, so SUMX operates only on rows that match the Sales Order.
