“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>, … )
This is basically anything that would itself define a measure. The following are all legal examples:
- SUM([Column1]) / MAX([Column2])
- 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.
- [ColumnName] = “Foo”
- [ColumnName] >= 6
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:
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 🙂