Linking two pivot tables with one slider

[Christof]

Hi,
I'm loving the slicers and slider features in recent Excel.
But I have a question. Is it possible to have one Slider control two seperate pivots that are each connected to a different data source?

I have a table of call stats, and a table of booking stats (nothing to link a call to a booking). But what I want to do it be able to choose a date range on my slider, and that will filter "Call Date" from my call stats, and "Booking Date" from my booking stats.

Is any of that possible?

 

[Chihiro]

Depends on the version of Excel you use and what tools are available to you.

However, if two table doesn't have linked field... why do you want slicer to tie the two?

Is Call Date & Booking date related somehow? Or you just want to show # of Calls & Bookings for a given date range?

If you need further help. I'd recommend uploading sample workbook with desensitized data.

 

[kritesh]

Steps to Connect a Slicer to Multiple Pivot Tables
Below are the steps which you need to follow to create a multi-linked slicer:

1. First of all, take two or more pivot tables to connect a slicer.
   
   
2. After that, select a cell in any of the pivot tables.
3. From here, go to Analyze → Filter → Insert Slicer.
4. Now from the “Insert Slicer” dialog box, select the column to use as a filterin the slicer and click OK.
   
   
5. At this point, you have a slicer in your worksheet which can filter the pivot table in which you insert it.
6. Next, you need to connect it to the second pivot table.
7. From here, select the slicer and go to Analyze → Slicer → Report Connections.
   
   
8. You will get a new dialog box with the list of pivot tables which are in your workbook.
9. In the end, just tick mark all the pivot tables and click OK.
   
   

Congratulations! you have connected the slicer with two pivot tables.

Hope this will helps you to connect your pivot table with one slider. If you want to enhance more skills then you can also do https://www.cetpainfotech.com/technology/advance-excel-trainingadvance excel training course in Noida

 

[Christof]

Hi, I have attached a sample file of what I'm trying to achieve. Basically looking to have both pivot tables controlled by one set of sliders/slicers at the top.. so if I select "Booked By" at the top, and choose a date range, both the pivot tables will update.
The "Confirmed Date" and the "Call Date" are the two date fields in each table that are connected.
Equally, a Brand can be assigned to the call and to the booking.

Hope this makes sense.

I'm using Excel version 365 ProPlus

 

[Chihiro]

Sorry, I can't open your file. Please upload just the raw data for both pivots.

I can show you how to build data model and to link the two.

 

[Christof]

Here you go... I've removed the pivots and replaced with a screenshot of something like what I'm trying to achieve.

Many thanks
Chrus

 

[Chihiro]

Ok, here's how I would do it.

1. Convert both range into Excel Table
NOTE: You have error value in [Staff] that should be replace with some valid string. I've used "N/A".
2. Go to data tab and "From Table/Range" and load both tables as connections only.
3. Go into Query Editor. Make sure both [Call Date] and [Confirmed Date] are Date data type.
4. Add blank query (named ListDate) and add following in formula bar.

= List.Distinct(List.Combine({Table1[Confirmed Date],Table2[Call Date]}))

5. Add another blank query (named Calendar) and add following in formula bar.

= List.Dates(#date(Date.Year(List.Min(ListDate)),1,1),Duration.Days(#date(Date.Year(List.Max(ListDate)),12,31)-#date(Date.Year(List.Min(ListDate)),1,1))+1,#duration(1, 0, 0, 0))

This will generate list of every date between Jan 1 of earliest date in list, to Dec 31 of latest date (year).
6. Using list tools, convert the list above into table. Change name and set data type to Date.
7. Add another blank query (Brand) and add following in formula bar. And convert result into table with text data type.

= List.Distinct(List.Combine({Table1[Brand],Table2[Cat]}))

8. Do the same for list of staff (Staff) using [Booked By] and [Staff]
9. Use "Close & Load to..." and set it to connection only.
10. Use Data -> Queries and Connection to show query pane. Right click on each of following queries and load to data model (Table1, Table2, Calendar, Brand, Staff)
11. Use PowerPivot->Manage and go into Diagram view and add relationship between tables like below. Note that it is 1 to many relationship going from Calendar, Staff and Brand to each table.


Then using Brand in Filter Field, Staff in Row Field, Dates in column field, create pivot tables. And add slicers and set connection to both.

See attached sample.

 

[Christof]

This is fantastic, and thank you for the detailed step-by-step.
This is my first attempt at power pivot, and you made it seem much simpler than I had feared.

Best Regards
Chris