Compare 2 Pivot Tables

[Kevin Labore]

Hello

I am wondering if there is a easy way to compare 2 pivot tables.
There have been many times where there is not a way to show a comparison of
2 sets of data in one pivot table.
What I have done in the past for scenario is to have a workbook that has 2
worksheets containing the 2 pivot tables.
I then have a 3rd worksheet that compares the data in the 2 pivot tables
using GETPIVOTDATA.
This work fine in most cases however setting up the 3rd sheet can take some
time.

One example of where I might want to compare 2 pivot tables: I have a Pivot
Table that shows the Score Test results of an assessment test By School,
Grade, Subject, Year. I want to show a view of how a particular school did
in comparison to ALL schools. While you can easily compare 2 Schools in a
pivot table there is not a way to create a view to compare ONE school to ALL
schools with the data I am comparing. As I mentioned having a separate
worksheet reference and compare the 2 pivot tables manually was the way to
accomplish this. Normally the Pivot tables have the same structure and the
page Field would be SchoolA on one pivot table and "ALL" for the 2nd pivot
table.

I have tried searching for info on this but haven't come across of any
explanation/examples of comparing 2 pivot tables(With a 3rd PivotTable)

Thanks in advance for any help

Kevin

 

[Roger Govier]

Hi Kevin

The following seemed to work for me
I set up a very simple 2 column table (the number of columns does not
matter)
Column A was headed School, Column B was headed Result
Random Data was entered with a letters for school and some figures for
result.
I then extracted a copy of the rows for School A to Sheet2.

When setting up the PT, I used >
Multiple Consolidation Ranges>Next>Create a Single Page field for
me>Next>Range>select the whole range from Sheet1>Add>Select smaller
range from Sheeet2>Add>Next>New worksheet>Finish

Now, for layout (it all seems counter intuitive, but it works)
Drag Row item to Page area
Drag Page Item to Column area
Drag Column item to Row Area
Drag Value item to Data Area and set type to Average or Sum or whatever
you want.

In the Column Area, Item 1 will be the set of Data for All schools
Item 2 will be the set of Data for school A appearing alongside it

Now from the PT toolbar you need to select Table Options and turn off
Grand Total for Rows and Gran Total for columns as they will be
incorrect as Scholl A's values will be double counted.

With all your columns of "results" appearing down the rows, you will
have you comparison of the Individual school that has been extracted to
sheet 2, with the overall total.
The Row item, which you have dragged to Page area would be left at All,
to pick up all schools. If you did select another school from the Page
dropdown, then just the results for that school would show as a single
column, apart from School A (in this case) where there would be 2
identical columns of data comparing itself with itself.

You could easily automate the extraction of the desired school to
Sheet2, using Advanced Filter.
For more help on this, take a look at Debra Dalgleish's site
http://www.contextures.com/xladvfilter01.html#ExtractWs

Debra also has some downloadable example files with code to automate
procedures, which you should be able to modify to suit your needs.

 

[Kevin Labore]

HI Roger

Thanks for the explanation
I figured there was simpler solution than my alternate solution which did
work, but was work to set up the 3rd sheet and do the referencing and
formatting

Kevin