Pivot Tables - Not allowing independent filtering

[hmltnangel]

Morning all,

Had a search and could not find an answer.

I have a sheet with three pivot tables on it. All sourcing data from the same table. The table has three company names within it. I want each pivot to show the data for the individual companies. However, if I select a company name in the Pivot Table field lists filters, then all three pivots are auto changed to the same selection.

Any suggestions? Or is it just not possible?

 

[RoryA]

That should not happen unless you are using a slicer connected to all three pivot tables.

 

[hmltnangel]

Thats just it, I thought "slicer" but I dont. Its really weird.

 

[RoryA]

Which version of Excel are you using, and have you loaded the data into the data model, or is it a regular pivot table?

 

[hmltnangel]

Hi Rory,

Which version of Excel are you using, and have you loaded the data into the data model, or is it a regular pivot table?

Excel 2016 and no - regular Pivot Table

Also, an update.

If I use the filter within Pivot Table fields, on the right. Then it only works for a single pivot table.

If I actually add the filter into the pivot table fields (by dragging it into the filter area) then it allows me the option to filter independently.

HOWEVER, when I do this then it loses the blank months for one of the companies, which doesn't look neat from a reporting perspective. (I would like all three to be the same. Any suggestions there?

 

[RoryA]

Oh, I see. Yes, if you filter the field in the field list, then it filters the source data cache and will apply to all pivots that use that cache.

It sounds like you need to set your month field to show items with no data.

 

[hmltnangel]

Thats what I though as well, but then if I do that .... then instead of just the last twelve months, it shows Blanks for the whole of 2019/2020. There must be a way to show just what I want ? ....it seems like I can get one or the other - but not the happy medium in between.

 

[RoryA]

You can just filter those out. Or if they are no longer part of your actual data set, you just need to set the pivot table to not retain old data items.

 

[hmltnangel]

Daft question Rory, but " set the pivot table to not retain old data items. " - hwo do I do that?

I cant filter them either, it wont let me - it allows filtering by month only - not yy/mm

 

[RoryA]

First, if you're going to cross-post, please follow the rules and provide the relevant links (both here and elsewhere).

You didn't mention that you had grouped by year and then month. If you do that, you're out of luck as your requirements become contradictory.