Forum Discussion
AVERAGEX with CALCULATE as a Quick Measure - why?
Hello friends,
I've just used a Quick Measure (AWESOME option!!!) to calculate Average per Category measure.
The DAX that was generated was:
AMT average per Dept =
AVERAGEX(KEEPFILTERS(VALUES('Fact'[Dept])), CALCULATE(SUM('Fact'[AMT])))
I does works fine, but I just don't understand why it uses CALCULATE inside AVERAGEX
If I omit CALCULATE:
AVERAGEX(KEEPFILTERS(VALUES('Fact'[Dept])), SUM('Fact'[AMT]))
I get wrong answer, but WHY?
If you have a link or an explanation - I will appreciate it!
Thank you!
Michael
Hi Anonymous
CALCULATE is required whenever you want to turn a row context into an equivalent filter context (context transition).
In your case, AVERAGEX iterates over the rows of VALUES('Fact'[Dept]), creating a row context for each row, and evaluates the 2nd argument in each row context in turn.
Without CALCULATE, the expression SUM('Fact'[AMT]) would have the same value for every row, being the sum evaluated in the original filter context. An aggregate expression like SUM('Fact'[AMT]) is calculated in the current filter context, which is not impacted by row context.
With CALCULATE, the row context, which in this case corresponds to a particular value of 'Fact'[Dept], is turned into a filter and added to the filter context, so SUM('Fact'[AMT]) is evaluated in that modified filter context, i.e. the sum is now filtered to a particular Dept.
I would suggest getting a copy of The Definitive Guide to DAX, or reading these articles (or others on the same topic):
- https://www.sqlbi.com/articles/understanding-context-transition/
- https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
- http://tinylizard.com/converting-row-context-into-filter-context/
(I have ignored the KEEPFILTERS part of the expression btw)
Regards
Owen :)
2 Replies
- OwenAuger
Super User
Hi Anonymous
CALCULATE is required whenever you want to turn a row context into an equivalent filter context (context transition).
In your case, AVERAGEX iterates over the rows of VALUES('Fact'[Dept]), creating a row context for each row, and evaluates the 2nd argument in each row context in turn.
Without CALCULATE, the expression SUM('Fact'[AMT]) would have the same value for every row, being the sum evaluated in the original filter context. An aggregate expression like SUM('Fact'[AMT]) is calculated in the current filter context, which is not impacted by row context.
With CALCULATE, the row context, which in this case corresponds to a particular value of 'Fact'[Dept], is turned into a filter and added to the filter context, so SUM('Fact'[AMT]) is evaluated in that modified filter context, i.e. the sum is now filtered to a particular Dept.
I would suggest getting a copy of The Definitive Guide to DAX, or reading these articles (or others on the same topic):
- https://www.sqlbi.com/articles/understanding-context-transition/
- https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/
- http://tinylizard.com/converting-row-context-into-filter-context/
(I have ignored the KEEPFILTERS part of the expression btw)
Regards
Owen :)