The following seemed to work for me
I set up a very simple 2 column table (the number of columns does not
Column A was headed School, Column B was headed Result
Random Data was entered with a letters for school and some figures for
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
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
Debra also has some downloadable example files with code to automate
procedures, which you should be able to modify to suit your needs.