In Excel, filters make it easy to exclude data from PivotTables. You can check off the desired categories, and watch the numbers change throughout:
The fancy word for this property is filter context — the data that gets returned is everywhere dependent on the context of the filter.
But what if you wanted to return data in this PivotTable that doesn’t change based on the filter, i.e. is not filter context-dependent? That’s where the ALL() function comes in.
To see when this might be helpful, and how to implement in Excel Power Pivot, download the following sales dataset:
Deriving a context-dependent distinct count
For this analysis, we’d like to create a measure that returns the total distinct count of orders, regardless of filter context. It’s never a bad idea to build measures step-by-step, so let’s start by building a context-dependent Distinct Orders measure with the with the DISTINCTCOUNT() function:
Nice work! Pulling distinct counts is one task where “Power” PivotTables really shine over the original. But some things are the same — filter this data by Region, for example, and you’ll see that the measure is filter context-dependent:
Filtering ALL() with CALCULATE()
OK, our goal is to modify the filter context of Distinct Orders so that it includes all relevant data. We can do that with a combination of ALL() with the “mother of all DAX functions,” CALCULATE(). According to the documentation, this function exists to “evaluate an expression in a modified filter context” — hey, exactly what we need!
Here’s how this will work: we’ll pass Distinct Orders into CALCULATE() as the measure we want to modify, then use ALL(orders) as the way we want to modify it.
We now have a measures All Orders that will calculate a distinct count of orders based on all the orders in the dataset, not just those in the filter context.
Go ahead and add this measure to your PivotTable, then try filtering it. All Orders doesn’t budge!
Use case: deriving a percent of total
If you’re asking when you’d ever want to clear the filter context of a measure, here’s one: taking the percentage to grand total. Let’s say we want to calculate each row’s contribution of distinct orders to the total. A Percent of all orders measure can do that:
Go ahead and drop this into the PivotTable and you’ll see the percentages neatly add up to 100%, as each row is being divided against the same filter-independent value:
To be fair, a percent to grand total measure could have also been derived by formatting how Distinct Orders is displayed in the PivotTable. But in Power Pivot, it’s a good idea to err on the side of explicit measures than implicit or reformatted ones.
How has the concept of filter context mattered to your Power Pivot models? Has the ALL() function come in handy and how? Let me know in the comments.
Leave a Reply