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 🙂

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