Context Transition in CALCULATE and Clarification of Correct Terms...

[mgirvin]

Dear Team,


Question #1:

If I create a Calculated Column with this aggregate calculation:

SUM-Aggregate =SUM(PlayersTable[Salary])

Is this a correct statement:

“Aggregate functions are affected by Filter Context, but will ignore Row Context and will show the same aggregated number in every row of the Calculated Column.”


Question #2:

If I create a Calculated Column with this aggregate calculation inside the CALCULATE function:

SUM-Aggregate-in-CALCULATE =CALCULATE(SUM(PlayersTable[Salary]))

Are these correct statements (these are my interpretations of Alberto Ferrari’s & Marco Russo’s book MS Excel 2013 Building Data Models with PowerPivot):

“An aggregate calculation inside CALCULATE will show just the row values not the aggregate values, and so if there is no relationship on this table, the Calculated Column will simply show the individual amounts for each row from the Salary column.”

“Inside CALCULATE, there is no Row Context”

“If you use only the first argument in CALCULATE, it will transform Row Context to Filter Context.”


Question #3:

If the three statements are TRUE from question #2, how do I make sense of them given that as a person new to PowerPivot I “see” CALCULATE returning individual row amount and thus think that CALCULATE is doing a Row Context”

Question #4:

If I create a Calculated Field:

TotalSalary:=SUM(PlayersTable[Salary])

And then create this Calculated Column:

SUM-CalculatedField-Auto-Call-To-CALCULATE =[TotalSalary]

Is this a correct statement:

“The Calculated Field comes with an automatic CALCULATE function wrapped around it and so because it is in a Calculated Column it will show just the row values and not the aggregated value”.

Sincerely, Mike "Struggling To Learn PowerPivot" Girvin

 

[scottsen]

Rob likes to say there are two types of people: Physicists and Construction Workers. The latter cares now about the coefficient of friction on the hammer...we just knows that if you whack stuff w/ it... it works. He is totally in the latter camp, and I'm generally closer to the earlier came... and periodically give up on certain concepts and just "go with it".

You are in the Physicist camp *and* like... the English Major camp

As a practical matter, this stuff is very rarely going to matter. Sometimes you will get the wrong results and you will be like "oh, I bet I need a calculate" (happens with RANKX to me).

#1: I would phrase as "in a calculated column there is no filter context, so the aggregate works across the entire table".
#2: CALCULATE will convert the row context, into a filter context of just the 1 row... and so, now there IS a filter context and the aggregate function (eg: SUM) will operate (by default) on just the one row.
#3: You have a filter context that exactly equals the 1 row
#4: Ya, measures have an implicit calculate -- a bit weird. Most likely to confuse me in RANKX().

 

[mgirvin]

Thank you for your continued help!

I am 100% not in the, as you call it, "Construction Workers" camp.