AVERAGEX with CALCULATE as a Quick Measure - why?

[Anonymous]

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

[OwenAuger · Accepted solution]

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 🙂

[Anonymous]

Thank you for the detailed explanation Owen!