PowerPivotPro

“No [doubt] she’ll freak.  I’m just contemplating the =IF()’s…”

   -Marcellus Wallace, obvious master of the spreadsheet arts

I can’t believe I didn’t say this last time:  =CALCULATE() is a lot like =SUMIF(), which is a function that Excel gurus know and love…  and sometimes hate :)  SUMIF and its cousins like COUNTIF and the plural SUMIFS are often indispensable.  When you want to perform an aggregation on a table, but just include rows that meet a certain criteria, the SUMIF family is often where you turn.

But SUMIF has a few limitations.  First of all, the conditional syntax is kinda awkward.  Second, if you want an aggregation that is not covered by the functions provided, you are out of luck – there is no MAXIF, for instance.  And you cannot use any of these functions inside a PivotTable, which, when you think about it, would be one of the most useful places to employ them.

=CALCULATE() fixes all of those limitations, and then does things you wouldn’t think to ask for :)

The syntax of CALCULATE()

     =CALCULATE(<aggregate expression>, <filter1>, <filter2>, … )

<aggregate expression>

This is basically anything that would itself define a measure.  The following are all legal examples:

1. SUM([Column])
2. SUM([Column1]) / MAX([Column2])
3. The name of another measure that’s already been defined

Pretty cool huh?  Literally you can CALCULATE on any aggregate expression you can dream up – even another measure that you defined before, like my “Avg Sales per Day” measure from the temperature mashup demo.

<filter1>, <filter2>, …

And then you can conditionally evaluate that aggregate expression based on any number of filters you’d like to apply.

Valid examples:

1. [ColumnName] = “Foo”
2. [ColumnName] >= 6
3. ALL([ColumnName])

Which is to say, that the syntax is exactly what you’d expect it to be :)

The power of ALL() is truly revolutionary

That ALL() thing is pretty unexpected though – it lets you create measures like “All-Time Sales” – if you set ALL([Date]) for instance, the resulting measure will respect all of the filters in the pivot table…  but not any filters on Date, meaning that even in a pivot sliced to Year = 2009, you could still see a measure that showed Sales for all years combined.  Useful in some cases for sure.

Of course, you can also create a CALCULATE expression that employs ALL() as a filter, then use that CALCULATE as the denominator of a measure.  Something like:

     =SUM(SalesTable[Sales]) /

     CALCULATE(SUM(SalesTable[Sales]), ALL(SalesTable[Sales]))

Would give you a measure like “Percentage of All-Time Sales.”

ALL() warrants its own post, and perhaps multiple posts, so I will revisit this later.

But in the meantime, back to football :)