Three pivot tables

[joshallen]

I have to create essentially the same pivot table 3 times, but the only variable is that I need one to show days, the other to show weeks, and the last to show months.

The problem I encounter is when I create the second pivot table and group by weeks, it makes the first pivot table do the same thing.

I tried using the pivot table wizard to create an un-linked 2nd and 3rd table, which solved the problem of making the other pivot tables change their grouping but then when I created slicers, the slicer only impacts one of the pivot tables and there is no way to link the slicer to the other pivot tables.

How do I get it where the pivot tables do not change when I change the grouping, so I can have one slicer for all of the pivot tables?

[#2 · Accepted solution]

@joshallen 

The easiest way is to add data to data model creating PivotTable

Other way is to separate caches, you may google for guidance, but I don't see practical reasons to use such method.

[#3]

Thank you! The add data to data model worked.

[#4]

@joshallen , you are welcome

[#5]

@Sergei Baklan 

Hi, I tried your method, but it didn't work. Do you have any other suggestions? When I try to group the second pivot table, a message appears saying: "it cannot group that selection". The group comes from the same numeric column as in the first pivot table, so I don't know what I'm doing wrong.

[#6]

@klacam 

Above works if you have hierarchy as dates (year, month, dates).

Grouping of numbers or texts doesn't work with pivot tables if data added to data model. You may do that without adding data to data model but separating pivot table caches. You may google for more details, simple approach is

Create pivot tables without adding data to data model

- open new empty Excel workbook

- cut one pivot table (e.g. second one) and paste into new workbook

- change here grouping as desired

- cut pivot table in new workbook and paste it back

- close new temporary workbook