Controlling multiple pivot tables with same slicers when data tables are many-to-many

rocket_dog

New Member
Joined
Apr 15, 2020
Messages
5
Office Version
  1. 2013
Platform
  1. Windows
I've been a long-time Excel user (25 years) and this one has me stumped. I think I've been staring at it too long or something, but I'll be darned if I can find a way to make it work. I've created a simplified simulation of my data, which consists of the following tables and columns:
  1. Revenue table
    1. Part #
    2. Category
    3. Customer
    4. Revenue
    5. Month
  2. Labor table
    1. Part #
    2. Labor
    3. Month
  3. Materials table
    1. Part #
    2. Materials
    3. Month
  4. Month table
    1. Month
  5. Parts table
    1. Part #
I've loaded everything into Power Pivot, and created relationships between the Month table and the other tables with a "Month" column, and I also created relationships between the Parts table and the other tables with a Part # column. So far so good.

Here's the problem: I need to be able to filter for "Category" and "Customer", at which point I need to see the Labor table and Materials get filtered by any Part # that matches the Category(ies) and/or Customer(s) that are being filtered by. Because all the tables contain multiple months, they also contain multiple Part #s, so I can't join the tables that way. Also, everything else is a many-to-many relationship: a Part # can appear in multiple Categories and have multiple Customers, a Category can have multiple Part #s and multiple Customers, and a Customer can have multiple Part #s and multiple Categories.

I realize that the Labor and Materials tables have no Category or Customer detail, so I would not expect to see those filtered to show only the Labor or Materials associated with a particular Category or Customer. However, what I would like to see is the Labor and Materials tables filtered to display only the Part #s that are associated with the Category and/or Customer that's being filtered (sliced).

For example, in my attached screen shot "pivots_filters_Showroom_Acme", you can see that the "Revenue" pivot table displays A3 but not B5 or C7, yet B5 and C7 are still appearing in the Labor and Materials pivot tables. What I want is for the Labor and Materials pivot tables to only reflect the Part #s that are available in the Revenue pivot table after the slicers have been selected.

Is there any way to use slicers to filter all three tables in this way?
 

Attachments

  • tbl_Revenue.PNG
    tbl_Revenue.PNG
    42.5 KB · Views: 2
  • tbl_Labor.PNG
    tbl_Labor.PNG
    20 KB · Views: 2
  • tbl_Materials.PNG
    tbl_Materials.PNG
    20.5 KB · Views: 1
  • tbl_Month.PNG
    tbl_Month.PNG
    3.3 KB · Views: 1
  • tbl_Parts.PNG
    tbl_Parts.PNG
    3.6 KB · Views: 3
  • power_pivot.PNG
    power_pivot.PNG
    19.8 KB · Views: 3
  • pivots_unfiltered.PNG
    pivots_unfiltered.PNG
    29.2 KB · Views: 3
  • pivots_filtered_January.PNG
    pivots_filtered_January.PNG
    23 KB · Views: 3
  • pivots_filtered_A3.PNG
    pivots_filtered_A3.PNG
    16 KB · Views: 3
  • pivots_filtered_Showroom_Acme.PNG
    pivots_filtered_Showroom_Acme.PNG
    19.6 KB · Views: 3

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Watch MrExcel Video

Forum statistics

Threads
1,114,478
Messages
5,548,268
Members
410,825
Latest member
Dave12
Top