|PowerPivot DAX: CALCULATE is a supercharged SUMIF|
|Written by Rob Collie|
|Tuesday, 15 December 2009 12:32|
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.
- Pareto Analysis the Easy Way
- Adding a Minimum Threshold Slicer to "Stores That went negative" Technique
- Showing Only Months/Weeks/Etc. When at Least N Stores Showed a Certain Behavior
- Named Sets and "Asymmetric" Pivots: Showing Different Measures for Different Years
- Toggling Conditional Formatting On/Off via Slicer
- Step by step guide on installing PowerPivot for SharePoint on a single machine
- List of suggested datasets to test PowerPivot
- How to install PowerPivot for Excel and list of know issues
- List of PowerPivot DAX functions with description
- Microsoft Virtual Lab: PowerPivot for Excel 2010 Introduction
- Learning PowerPivot and DAX
- List of PowerPivot DAX functions (short)
- PowerPivot's impact on BI pros?