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

This entry was posted on Monday, December 14th, 2009 at 8:38 pm and is filed under DAX. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.