Pivot Table Row Label Filter based on another Pivot Table

Koxeida

Board Regular
Joined
Oct 25, 2016
Messages
73
Hey guys,

I have two pivot table which are created from Power Pivot.

Pivot Table A consists of Manufacturing plant (Row label) and Product Category (Column label) with value field representing whether the particular Product Category from the specific plant is allowed to be exported to a Country destination (Slicer 1). That particular slicer is disconnected to Pivot Table B and I will explain why later

Pivot Table B consists of Manufacturing plant (Row label), Product Data (a tier down in row label) and Country destination (Column label) with value field representing the total no. of existing products (Product Category > Product in terms of hierarchy) in the specific country. (Basically they are existing items being sold in the market). There is also a Slicer 2 which represents the Manufacturing plant and is connected to both tables

The main objective of these 2 tables are:
  1. Select Slicer 1. For example, I have selected Norway
  2. Pivot Table A will be filtered accordingly and return a list of Manufacturing plant with corresponding Product category that are currently being allowed to be exported to Norway. Pivot Table B will not be affected.
  3. Slicer 2 is also filtered based on the Manufacturing plants being removed.
  4. Next, the user will select a specific Manufacturing plant in Slicer 2. The user will know which plant to select by referring to the filtered Table A
  5. Now we see all the relevant products that are currently being allowed to be exported to.

Overall, when I execute these steps, I want to understand what are the Manufacturing products and its corresponding Product categories that have been approved currently in Norway. Table B allows me to understand what are the other similar products that is currently approved in Norway However there is one big issue.


  1. Manufacturing Plants in Table A and B are not in sync as the main connection is dependent on the Country destination and Destination slicer is desync to Table B. Both are row labels

As such, would it be possible for the Manufacturing Plants in Table B to be automatically sorted when I select Slicer 1? Basically, filtering the Row label in Table B based on the filtered Row label in Table A (which is filtered from selecting Slicer 1).

In addition, could the Slicer 2 only show data from Table A but not Table B although selecting it will filter both tables?


This is quite a complex situation on hand and I'd appreciate any help I could get. Thanks guys! :)
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,339
Messages
6,171,532
Members
452,409
Latest member
brychu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top