PowerPivot Slicers Shapes and groups

[Bodders]

I have an Excel 2010 pivot table based on 3 Power Pivot tables, which summarises goods moving on certain dates from one region/country into another region/country. An example would be 500 units in Jan 2015 moving from Europe/UK to Asia/Singapore.

For the pivot table I would like to have 2 separate groups of 2 linked slicers – one to select the Source Region and Source Country, and the other to select the Destination Region and Destination Country. When I select the 4 pivot fields to be slicers they all go inside one shape. If I cut and paste one the slicers it then shows as unchecked on the PowerPivot Field List. How can I get them into 2 separate slicer groups ?

A further issue is that the formatting of the slicers does not seem to permanent. For instance, I select one slicer to have 2 columns, I then click on the slicer and it goes back to having 1 column again. Any ideas on this ?

Thanks for your help

Bodders

 

[Matt Allington]

You can use disconnected slicers. Create one table for each slicer. Include all values for the slicer but don't join it to anything. You can then extract the selected value with a harvester measure.

Selection=if(hasonevalue(table[column]),values(table[column]))

then use the selection measure in your other measures to do what you want.

Excel 2010 is 7 years old and the slicer integration is quirky. You can click in a pivot and then Insert\slicer to create one that doesn't behave this way.

 

[Bodders]

Matt

Thanks for that. The tip about using the Insert Slicer from the ribbon works a treat. The slicers keep their formatting. The only thing is that they do not show in the pane for the Pivot Table Field List, but if they work anyway it's not a problem for me. We should be upgrading to Excel 2016 in early 2017 so that should improve a lot of things anyway.

Bodders