1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Linking two pivot tables with one slider

Discussion in 'Excel Dashboards' started by Christof, Jan 31, 2019.

  1. Christof

    Christof Member

    Messages:
    47
    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?
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,242
    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.
  3. kritesh

    kritesh New Member

    Messages:
    2
    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.
      [​IMG]
    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.
      [​IMG]
    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.
      [​IMG]
    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.
      [​IMG]
    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
  4. Christof

    Christof Member

    Messages:
    47
    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

    Attached Files:

  5. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,242
    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.
  6. Christof

    Christof Member

    Messages:
    47
    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

    Attached Files:

  7. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,242
    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.
    Code (vb):
    = List.Distinct(List.Combine({Table1[Confirmed Date],Table2[Call Date]}))
    5. Add another blank query (named Calendar) and add following in formula bar.
    Code (vb):
    = 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.
    Code (vb):
    = 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.
    upload_2019-2-15_14-25-27.png

    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.

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page