• 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...

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

Pivot Slicer to work for pivot tables based on two sets of data

K

Kully Rai

New Member
Hi,

I have been using pivot table slicers for a large set of data for active employees and everything works fine. I now have another set of data for employees who have left in another tab and have a pivot table based on this set of data. On my dashboard I am trying to show a summary of active employees as well as those that have left but as my pivots tables are based on two separate sets of data, the slicer connections do not work for both.

The slicer fields i want to use, appear in both sets of data (country, area, job title etc.). Is there any way i can get the one set of slicers to work on both sets of pivot tables?

I have tried to search for a solution but cannot find anything. I'm not sure if i can pass the value from my current slicers (based on active employees) and pass that onto slicers based on my leavers data? Any help would be much appreciated. Thanks.

Kully
 
Hey Kully,

Please refer below url for one slicer for multiple pivot table


http://myexcelonline.com/blog/connect-slicers-to-multiple-excel-pivot-tables/

https://exceljet.net/tips/how-to-use-a-slicer-for-multiple-pivot-tables


Kully Rai said:
Hi,

I have been using pivot table slicers for a large set of data for active employees and everything works fine. I now have another set of data for employees who have left in another tab and have a pivot table based on this set of data. On my dashboard I am trying to show a summary of active employees as well as those that have left but as my pivots tables are based on two separate sets of data, the slicer connections do not work for both.

The slicer fields i want to use, appear in both sets of data (country, area, job title etc.). Is there any way i can get the one set of slicers to work on both sets of pivot tables?

I have tried to search for a solution but cannot find anything. I'm not sure if i can pass the value from my current slicers (based on active employees) and pass that onto slicers based on my leavers data? Any help would be much appreciated. Thanks.

Kully
Click to expand...
 
Thank you for the links above xlstime. However, they address how to connect the slicers to multiple pivot tables when the pivots are all based on the same data set.

What i have is pivot tables that are based on two separate sets of data. Both sets of data have some common fields like Country, Job Title etc. I want one set of filters (based on first data set) to work on the pivot tables that are based on the other data set. Any help would be much appreciated.
 
There's few ways I can think of.

1. If two data sets can be related directly, use data relations to link the two and add to data model. This way you can use slicer to control both.

2. Create separate table which contain slicer item columns only. Use that to build your slicer, then use VBA to read selection and apply to existing Pivots as filter.

3. Add index table as third source, use that to link existing data sources and add to data model. Re-build pivot using newly created table as field source.
 
Thank you Chihiro. My Excel knowledge is good but i am not advanced and my VBA knowledge is very minimal so you may need to explain this to me.

I'm not sure if the two sets of data can be related. They are based on employees - one set of data shows all the data for active employees and the other data set are employees who have left the company. However, they both have field Employee ID so can i use that to relate them? If so ,what is the process? There are only around 6 fields in common in both data sets and those are the only slicers i want to use so they are applicable to both data sets.

I have to be wary that my end users will not have add-ons like Power Pivot so i need this to work for all Excel users.
 
If your office use Excel 2013 and later, Data model comes as default feature without any add-on. For Excel 2010, you need add-on.

From the sound of it, it should be fairly easy to combine your data into one table (even without data model). If you can upload some sample data (say about 10 each for active and non-active), it'd help.

I'll be bit busy rest of today with meetings, but can take a look at it later in the day.
 
Thank you! I have Excel 2013.

In the attached file i have stripped a lot of my original data so and left just a few fields as an example.

Tab 'Data' is my existing employees
Tab 'Leavers' is my second data set of employees who left
Tab 'HC Pivot' is where i have created the pivots that I want to filter (left pivots are active employees and right pivots are leavers)
Tab 'Workforce Map' is where i want the user to select their filters to work on both sets of data.

Thank you for taking the time to look at this.

Kully
 

Attachments

  • Workforce Example.xlsm
    135.5 KB · Views: 8
Added "Index" sheet which has table of indexed Band (tblBand).

Both Data and Leavers were converted to Table (tblData & tblLeavers).

Also created in "Index" sheet indexed Country list (tblCountry).

Then using tblBand as Related Lookup Table, constructed Data Relationships with both tblData and tblLeavers "Band" column. Repeated same process for tblCountry.
upload_2016-8-3_9-52-16.png


Pivots were created using above data relationship and added Country (from tblCountry) as filter field to both pivots. Slicer was created and Report Connection was made to both pivots.

See Sheet3 for demo of slicer.

Edit: See below for good explanation on how to build data relations.
http://www.techrepublic.com/blog/10-things/10-steps-to-using-excel-2013s-new-data-modeling-feature/
 

Attachments

  • Workforce Example (1).xlsm
    540.6 KB · Views: 30
Thank you so much Chihiro! I worked through the example link you sent to just understand the process which was really useful and then applied this to my main file as you described and it works great (after a few head scratching moments!).

Really appreciate your time and assistance in this and i learnt something new! :)
 
You must log in or register to reply here.
Back
Top