Hello all and welcome back to the Excel Tip of the Week! This week we have a Developer post in which we’re taking a look at a common request for PivotTables – a way to easily switch between different ways of summarising the data. This does require the use of Power Pivot Measures, which are reasonably complex, but the end result is simple to understand for the end user.
You can get a refresher on Measures in TOTW #369.
What we want to be able to do
We’re working with this simple example dataset:
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:
Excel community
This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.
