Excel allows you to connect a slicer to multiple data sources using the power of Power Pivot. In the past a slicer could connect to multiple pivot tables provided those pivot tables shared the same source data. With the advent of Power Pivot there is a way to have separate tables that share a common field and link that field in the Diagram View of Power Pivot so that the tables are able to talk to one another.
The following YouTube video takes you through the process from start to finish.
The File
Setting up Power Pivot
First things first - we need to sent a minimum of 3 tables to Power Pivot. In the following example I will send the revenue table, the expense table and finally the region or location table. We will connect these 3 tables together in the diagram view.
Open the file - Create 3 tables.
Location - on the Region Tab.
Revenue - on the Revenue Tab.
Expense - on the Expense Tab.
From the Power Pivot Menu - Choose Add to Data Model.
Do the same for all 3 tables and you will have all 3 data sets in the Power Pivot back end.
Choose Diagram View
Create a connection between.
Location - Region to Expense Region
Drag and drop the region as shown above.
A connection between the two tables is created. Do the same for the Revenue table.
The table should look like the above with two connections between the Location and Expense and Revenue.
Now Insert a 2 PivotTables from the Home menu. One from the Revenue table and one from the Expense table.
Be sure to use the Location table as the filter for both pivot tables, one from Revenue and one from Expense.
Insert the Slicer
Inserting the slicer is the second last part of the process. Click inside any of the 2 pivot tables and choose the Analyze menu.
Choose Insert Slicer. This will bring up the following dialog.
Be sure to tick Region from the Location table. This will give you access to both tables as the connection was set up in Power Pivot.
The above slicer is created.
Now right click the Slicer and choose Report Connections.
Be sure to choose both the Revenue and Expense Pivot tables. This should allow you to control both pivot tables from two completely different data sources.
