NOTE: This will increase the file size as we have double the amount of data due to the separate caches. Cut this second PivotTable again from the new workbook and paste it into the original workbook.īoth PivotTables are based on separate caches now, which means you can change the grouping in one PivotTable without affecting the other.Change the grouping style on the second PivotTable that is now in a new workbook.Go to a new workbook and paste it in (Ctrl V).Highlight the area of cells containing your second PivotTable and cut it (Ctrl X).“HOW DO I GET IT TO WORK?” I hear you cry! You just need to do this: This is because they are based on the same source data and therefore share the same cache. If I change the grouping from months to quarters in one PivotTable, it will also change in the other. The image above shows my two PivotTables and in both, the individual dates have been grouped by month, using the Group Field feature. PivotTable grouping is easy enough to do, but it could cause a lot of frustation if you want to change the grouping in one table without changing the way that field is grouped in another table. Using the data below as an example, I want to see a distinct count of each colour for each date.This post is aimed at those who already use PivotTables (as many of my trainees do!).
How can I use both the Distinct Count and Group by Number of Days options together? There doesn't seem to be a problem with the data as when I create the PivotTable from the data model it automatically groups them by month, so it must be seeing the data as dates. If I add the data to the data model then when I create the PivotTable from the Data Model I can use Distinct Count but I can't If I create a PivotTable without adding the data to the Data Model, the Distinct Count option for the Values is not available. I would like to see how many distinct values there are for each date, but with the dates Grouped by Week, i.e. I am trying to create a PivotTable based on data which includes dates.
0 kommentar(er)
