A Power BI Technique Mined from the Power Pivot Archives
Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI. Hard to imagine - even for us - but true.
Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.”
Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables. Enjoy!
“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>, … )
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 🙂
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://powerpivotpro.com/2014/03/becoming-one-with-calculate/. 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