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

-Marcellus Wallace, obvious master of the spreadsheet arts

## CALCULATE is a supercharged SUMIF

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:

- SUM([Column])
- 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.

Valid examples:

- [ColumnName] = “Foo”
- [ColumnName] >= 6
- 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 🙂

So now you know that CALCULATE is a supercharged SUMIF. If you liked this post and want to read more about CALCULATE, here’s a great next post: https://p3adaptive.com. If that post was too human and you want to read a description of CALCULATE written for robots, please go here: https://msdn.microsoft.com/en-us/library/ee634825.aspx

Get in touch with a P3 team member