Change PivotChart Aggregation Methods using Excel Slicers

With disconnected tables in Power Pivot* we can change PivotChart aggregation methods using Excel Slicers. It allows us to create Pivot Charts where the user can select different views of the data like so:

It’s handy if you have limited space in your report or dashboard, as it allows you fit two charts into one.

*Click here to see if your version of Excel supports Power Pivot.

This post assumes you are familiar with Power Pivot (aka the Data Model), can load data to the model and create relationships. If you’d like to learn Power Pivot, please consider my Power Pivot course.

Power Pivot Model Structure

The model contains a table for the facts i.e. the transaction data, called ‘Sales’. There’s also a Calendar table because the sales transactions are not on consecutive dates, which are required for the prior period measures below. And finally a table for the aggregation type.

Tip: Notice that the Aggregation table is not connected to any other tables.

Power Pivot Measures

There are four measures that aggregate the sales values for the current period and prior period:

You can create more measures if required.

Disconnected Table

The trick to this is creating a disconnected table that we use to feed the Slicer. It’s just a Table in Excel that lists the measures/aggregations that I want the user to choose from, which I’ve loaded into the Power Pivot model.

Remember, this table doesn’t get connected to any other tables in the model.

Next you need to set up a measure for this table that detects the aggregation number selected in the Slicer. Mine is called ‘Selected Measure’:

=MIN(Aggregation[Aggregation Number])

Then create another measure with the SWITCH function that takes the ‘Selected Measure’ value and returns the corresponding measure. Mine is called ‘Measure for Chart’:

=SWITCH(
[Selected Measure],
1, [Sales Current Period],
2, [Sales Prior Period],
3, [Avg. Sales Current Period],
4, [Avg. Sales Prior Period]
)

PivotTable

The PivotTable uses the field; ‘Aggregation’ in the column labels and the field; ‘Measure for Chart’ in the Values area. I’ve used dates in my row labels because my Sales table prior period measures require them.

Note: You can ignore the Relationship warning in the field list that appears when you add ‘Aggregation’ and ‘Measure for Chart’ to the PivotTable.

PivotChart

And finally, insert your Slicer for the Aggregation type (and any others you want), and the Pivot Chart:

Download Workbook

Thanks

Thanks to Erik Svensen for sharing this idea.