Excel Tip of the Week #388 - Power Pivot: Filtering measures

You can get a refresher on Measures in TOTW #369.

What we want to be able to do

It’s pretty simple to make a PivotTable from this data that displays the sum of the Value column for each Label, and add a Slicer that allows the user to select from the different Labels with the click of a button:

We can equally easily filter any column labels in a Pivot, or use a Report Filter to filter which rows of the original data are included in the first place. But what about the fourth area of the PivotTables menu – the Values area? We can easily include multiple different summaries of the data:

But these can’t be filtered – at least, not easily. But it is possible!

The solution requires Power Pivot to work, so we will start by loading our table into the Data Model and creating the three summary operations we want to choose between as simple Measures:

These are simply created with the SUMX, AVERAGEX, and MAXX DAX functions.

Building our filter

Our approach is going to be to build a new measure that returns one of the three simple measures based on a selection the user makes in a filter. Now, our measures don’t appear in anything that can be filtered right now – so we are going to have to make a table that does contain them. Unfortunately we just have to do this manually by making a table:

Note that we can use whatever name we want for the operations here – they don’t have to match the actual names of the measures. This means we can choose familiar, intuitive names for them. Note also that we have included an index column that has a number for each – this is what we will be using to actually pick the operation used in our final measure. We then load this table into the Data Model – in our example it’s simply called “A”.

Now for the sneaky bit – building our final measure. In the end product, there will be a Slicer in place that means that only one row of table A is ever active at once. So we need a way to find which row that is, and then return the corresponding measure.

We’re going to do this by taking advantage of some new Measure syntax – the ability to create custom variables. Here’s the full measure:

Let’s break that down:

Slicer Selected Measure:=var selID = MIN(A[Index])

We start by giving our Measure a name and then using the var operator to define a variable – in this case called selID. The variable is defined with a MIN function that looks at the Index column of table A. The use of MIN is fairly arbitrary here as we’re only expecting there to be one row of table A active under the filter at any one time, but using something like MIN instead of SUM means that something will be returned even if the user clears the filter.

Next:

RETURN

This is part of the DAX syntax that tells Power Pivot that we’re done defining variables, and are now ready to use them in the definition of the output for the actual Measure.

SWITCH(selID,1,[SumofValues],2,[AverageofValues],3,[MaxofValues])

SWITCH is actually a standard Excel formula – see TOTW #330 for more – but essentially, it can just take a value and spit out one of a selection of outputs based on that value. Here we are switching selID – our user’s selected value from the filter – and then for each ID, we manually specify which of our pre-existing Measures should be used.
Finally, we create our Pivot and then insert a Filter on the A table Operation column:

A couple of final notes:

1) You can’t change the column heading in the Pivot to match whatever the user picks in the filter, but you can name it something that makes the connection clear

2) Excel will display the “Relationships between tables may be needed” warning, as the connection between the two tables we are using is too subtle for it to notice – this isn’t an issue and the warning can be dismissed safely

3) If you want to add to or amend the list of operations later on, don’t forget you need to update both the table and then rewrite the list in the SWITCH part of our Measure

You can download the file and see the finished product for yourself:

• Download Excel file

• Previous Article - 387 - Revisiting fixing broken links
• TOTW index
• Next time – Power queries: dropping rows and columns