Syncing Multiple Slicers to Pivot Tables from Different Data Sources|Power Pivot|Excel Forum|My Online Training Hub

Hi Mynda,

I documented what I wanted to do via an example.  I've got 2 pivot tables and 2 slicers.  If you filter via slicer #1, it updates both pivot tables as well as the slicer #2.  If you filter slicer #2, it also updates both pivot tables, as well as slicer #1.    

The example I provided has to do with allocations of people's time.  I also included people salaries, so one could eventually use Power Query to perform some additional calculations of how much spending in incurred in particular divisions of a company, depending on how much of a person's time is spent working for that division.  That part of the issue wasn't pertinent to this problem, so I excluded it from the example.  

So, I have 2 questions. 

1) Rather than craft a solution via macros, is there a better way to do this via Power Query and the data model? 

2) This 2nd question may only make sense if you follow along in the example.  I provided a 3rd list in the file although I didn't create a 3rd pivot table.  If I did, you would see that the field for 'Person' is NOT part of this 3rd pivot table.  Let's say I created the 3rd pivot able and also added a 3rd slicer (for Company) attached to the 3rd pivot table.  Since 'Company' isn't part of the 2nd pivot table, I couldn't use the macro method to update the 1st and 2nd pivot tables.  What I'd like to see happen is that I use the 3rd slicer to filter on Company One.  This would only show Division A in the 3rd pivot table.  Then, because only Division A is in the 3rd pivot table, I want to see the 2nd pivot table also show only Division A.  That also corresponds to the Activity field of 'MFG & R&D' in the 2nd pivot table.  Then, in the 1st pivot table, I'd like to see Bob and Jim listed because they work in the Activity of 'MFG & R&D'.   

Commentary for Question #2.  You may ask why I don't just create one big pivot table and then just connect all the desired slicers to that one table.  I could do that, but I'm concerned that this example won't scale.  Instead of 4 people, let's say there were 1,000.  And instead of 1 or 2 Activities where people could charge their time to, let's say there were up to 20.  You can see that the final pivot table would get big.  It still might be small enough for a simple pivot table or a Power Pivot table to handle it, but I was hoping to develop a solution that didn't drag along every field from the original (1st) table to the subsequent tables.  

Hopefully, that makes sense via email.

Thanks!

Hi Mynda,

Ah, sorry about that.  I was playing around with that 3rd table, but forgot to change it back.  What I meant to portray was that there was a 1:many relationship between Division and Company too.  Please see the picture in the attachment for the correct row values.

In thinking about this problem further, I can envision the problem in one of several ways:

1. In addition to a slicer filtering a pivot table, it's a problem of that pivot table then filtering another slicer that is connected to a pivot table from a different data source. OR...

2. In VBA terms, it's a matter of a pivot table item updating a slicer item.  Even if this could be done via VBA code, that sounds more like brain surgery to me!  OR...

3. From a Data Model point of view, I know that one helper (unique) table can connect to 2 (many) tables and then you can use a slicer connected to that helper tables to drive what you see in pivots of the other 2 (many) tables.  [The caveat there is that you have to include the field in the helper table in what you display in those separate pivot tables, per your teaching].  In that scenario, I believe the connection between the helper table and the other 2 tables would 1-way connections.  What I suppose I am looking for is a 2-way connection between one of those (many) tables and another slicer (or helper table, I suppose) that is connected to a different data source.  As someone who is no expert in using complex data models, I don't know if this is possible.

Although I've separately crafted a solution using "1 big table" with every data field in it, my concern, per my previous message, is that the solution won't scale.  Additionally, I feel it goes against everything I learned about having a database architecture that is parsimonious i.e. not repeating data that is stored in another table.

Hopefully, all those analogies help you visualize what I'm trying to do with a more complicated hierarchy of data than just Person -> Activity -> Division -> Company.

Regards,

Scotty81

Hi Mynda,

I'm only a database novice, but I attached a few database details. I added them in with this copy of the original file to keep all this together. The pertinent tabs now are:

Schema with Separate Tables - the schema of what I consider to be the fact and dimension tables. This is too simplified though since it excludes all the calculated columns I add to the original fact table.

Schema with One Big Table - I think this is what you asked for. The "one big table" is located in columns AE-AO, although you can see the walkup of how I get there from starting in column A. In the top rows, I list just the tables that I would build up if I were constructing them via Power Query. In the lower rows is data that came from my original example, culminating with the example for the "one big table". I might have changed a data element or two, but the essence of it is intact. The green cells are data one would enter, and the grey cells are either calculated data or what I think of as foreign keys to the other tables.

Outputs - Since I think it is important to see what I'm trying to accomplish, I included pivot tables - all based on that one big table. This allows me to connect all the slicers to all the tables since all the PTs come from the same pivot cache. I had to coax the PTs to get them to do what I wanted, but I think it works. And although I think it does, I don't think this solution would scale if I had 500 people charging time to many categories for multiple divisions.

I hope the one big table, the walkup and the output pivots all make sense. Let me know if it doesn't.  Thanks again for seeing if this solution can be improved upon.

Regards,

Scotty81

Hi Mynda,

Thanks so much for providing an example file with the revised dimension and fact tables.  Your response about the table structure made sense to me.  And if I just keep the 'one big' data table as a connection and display the result as (a series) of pivot tables, I realize I won't have to ever list all the rows as output.  

I still have to play around with the measures.  The measures actually all need to add up to the same cost since it is the same 2 people whose salaries total up to $200 K in my example, no matter how much you slice it.  I think I can do that by counting the number of visible rows in the filtered table and/or creating some keys (e.g. Name.Activity) and playing around with those as well.  

The reason I used averages in my pivot table (which you commented on) was that I was trying to preserve certain values in the pivot table, regardless of some of the filters I was using.  So, if i used the traditional 'sum' of Activity Cost, for example, of 2 rows of $40 K, I'd get $80 K, which is not what I wanted.  By using an average, the table averages $40 K and $40 K, which is still $40 K.  To total the value column in the pivot table, I was having to perform a sum outside the pivot.  That method in itself is a squirly workaround to what I was trying to do.  So, I'll need to work on that too.  

What you did was to help me understand my problem better, as well as provide a framework for other similar challenges.  

Many thanks!

Scotty