Excel 2007 : pivot table subtotals are not being displayed

**cjphelps** · 11-01-2009, 11:32 PM

I have created a small pivot table in Excel 2007,

In the row labels, I have 3 stores.

I would like to display information below each store as follows:

Store1
Average Profit of Store1
# of Sales
Total Sales by Store1
Store2
Average Profit of Store2
# of Sales
Total Sales by Store2
...
...
...
I thought this would be accomplished by changing the field settings and choosing custom subtotals, But they are not showing up!!! I have now spent 5 hours researching the net, and reading the Excel help over and over.
Does anyone have any suggestions? Am I missing something?

Best regards

**DonkeyOte** · 11-02-2009, 03:24 AM

Might be an idea to post a small dummy sample file - it's not very clear (to me) from your post as to what your data fields are etc...

**cjphelps** · 11-02-2009, 06:11 AM

here is a csv snippet. the bug problem is getting the file to display the subtotals after a pivot table is created,

Genus,Species,***,City,Store Cost,Selling Price,Profit,Date,Month,Order No,Supplier,Random No
Parrot,African Grey,U,Cote St Luc, 800 ," 1,399 ", 599 ,20-Nov-08, Nov , 40 , Pet Bird Co , 33
Parrot,African Grey,M,Pierrefonds, 45 , 49 , 4 ,17-Nov-08, Nov , 37 , Pet Bird Co , 43
Parrot,African Grey,U,Cote St Luc, 750 ," 1,349 ", 599 ,16-Nov-08, Nov , 36 , Pet Bird Co , 23
Parrot,African Grey,U,Dollard, 600 , 749 , 149 ,10-Nov-08, Nov , 30 , Independant , 29
Parrot,African Grey,F,Pierrefonds, 500 , 999 , 499 ,5-Nov-08, Nov , 25 , Pet Bird Co , 24
Parrot,Dusky Pionus,U,Dorval, 500 , 999 , 499 ,4-Nov-08, Nov , 24 , Independant , 4
Parrot,Dusky Pionus,U,Pierrefonds, 450 , 799 , 349 ,31-Oct-08, Oct , 20 , Pet Bird Co , 8
Parrot,Dusky Pionus,M,Dorval, 425 , 799 , 374 ,27-Oct-08, Oct , 16 , Pet Bird Co , 3
Parrot,Jardine,U,Montreal, 450 , 699 , 249 ,2-Nov-08, Nov , 22 , Pet Bird Co , 10
Parrot,Jardine,M,Pierrefonds, 450 , 699 , 249 ,1-Nov-08, Nov , 21 , Independant , 25
Parrot,Jardine,F,Dollard, 450 , 699 , 249 ,29-Oct-08, Oct , 18 , Royal Bird Co , 37
Lovebird,Dutch Blue,U,Cote St Luc, 100 , 150 , 50 ,24-Oct-08, Oct , 13 , Pet Bird Co , 44
Lovebird,Peach-Face,M,Dorval, 45 , 80 , 35 ,24-Nov-08, Nov , 44 , Pet Bird Co , 7
Lovebird,Peach-Face,U,Dollard, 55 , 70 , 15 ,23-Nov-08, Nov , 43 , Pet Bird Co , 16

**cjphelps** · 11-02-2009, 06:12 AM

oh and the *** in the CSV is the birds S%ex, the forum Does not allow that word to be posted...

**DonkeyOte** · 11-02-2009, 06:45 AM

If (and I mean If) I've understood... your PT is setup such that

Row Label: City

Data Fields: Profit (Average), City (Count), Selling Price (Sum)

At present the Data Fields appear horizontally adjacent to the City label as opposed to vertically beneath as is your want, is that correct ?

If so...

If you look at your PT you will see in Column Labels an item called Values, drag this from Column Labels to Row Labels and place beneath City.
Then with the PT active, via the Design tab on the Ribbon, set Report Layout to Compact Form (if not already).

I hope that helps.