Multiple columns in pivot table reset running total

[txdore]

I created a pivot chart with a running total. In the first example below [sorry, IT doesn't allow me to use the HTML maker], the running total of the for that category works fine (Amount, Show data as: running total in, base field: Date).

FYI, the fields Category, Date, & Amount are in the columns. Amount is also in the data field. And I am using 2003.

Sum of Amount
Category,Date,Amount,Running Total
Gas,1-Apr,(blank),0
,07-Apr,50,50
,09-Apr,50,100
,12-Apr,50,150
,13-Apr,50,200
,18-Apr,50,250
,23-Apr,50,300
,10-Apr,50,350
Gas Total,,,
Gro,01-Apr,(blank),0
,02-Apr,25,25
,08-Apr,25,50
,12-Apr,25,75
,15-Apr,25,100
,22-Apr,25,125
Gro Total,,,
Grand Total,,,

But I want to add some more fields. (I didn't find any other posts addressing this issue.) I want to see the date, vendor, and amount of each transaction AND the running total for the category. When I add the Vendor field to the columns as shown below, the running total stops working. I tried changing the base field some, but nothing works. It does weird things, like if the base category is set to vendor, it sums the vendors even though it's out of order. Any ideas??

Sum of Amount
Category,Date,Vendor,Amount,Running Total
Gas,07-Apr,Chevron,50,50
,09-Apr,Sam's Club,50,50
,12-Apr,Chevron,50,50
,13-Apr,Sam's Club,50,50
,18-Apr,Chevron,50,50
,23-Apr,Chevron,50,50
,10-Apr,Chevron,50,50
Gas Total,,,,350
Gro,02-Apr,Kroger,25,25
,08-Apr,Tom Thumb,25,25
,12-Apr,Kroger,25,25
,15-Apr,Kroger,25,25
,22-Apr,Tom Thumb,25,25
Gro Total,,,,475
Grand Total,,,,

Data:
Date,Vendor,Category,Amount
02-Apr,Kroger,Groceries,25
06-Apr,Allstate,Insurance,120
07-Apr,Chevron,Gas,50
08-Apr,Tom Thumb,Groceries,25
10-Apr,Chevron,Gas,50
09-Apr,Sam's,Gas,50
12-Apr,Chevron,Gas,50
12-Apr,Kroger,Groceries,25
13-Apr,Sam's,Gas,50
15-Apr,Kroger,Groceries,25
18-Apr,Chevron,Gas,50
22-Apr,Tom Thumb,Groceries,25
23-Apr,Chevron,Gas,50

 

[cyrilbrd]

Hi,

IT doesn't allow me to use the HTML maker

Try to use the Wrap HTML built up with the Message box. It it the icon that looks like this "<>",second to the last among all the small icons fro font, indent, link...

it should have taken you you a while to type all of this...

What version of Excel are you using?
Thnks.

 

[txdore]

Cut and paste is our friend.
Funny, I don't see any font tools at all.
Anyway, I'm doing this from the house how. I know it says 2010 below, but I am using office 2003 at the office!!

This is what I have...

Excel 2010
	A	B	C	D
3	Sum of Amount
4	Category	Date	Amount	Total
5	Gas	1-Apr	(blank)	0
6		7-Apr	50	50
7		9-Apr	50	100
8		10-Apr	50	150
9		12-Apr	50	200
10		13-Apr	50	250
11		18-Apr	50	300
12		23-Apr	50	350
13	Gas Total
14	Groceries	1-Apr	(blank)	0
15		2-Apr	25	25
16		8-Apr	25	50
17		12-Apr	25	75
18		15-Apr	25	100
19		22-Apr	25	125
20	Groceries Total
21	Insurance	1-Apr	(blank)	0
22		6-Apr	120	120
23	Insurance Total
24	Grand Total
25
26
Table

This is what I want, except the running total isn't working. It's keeping a running total for the vendor and not the entire category.
Like I said before, the category, vendor, date, and amount are all in the column field of the pivot table.

Excel 2010
	H	I	J	K	L
3	Sum of Amount
4	Category	Date	Vendor	Amount	Running Total
5	Gas	7-Apr	Chevron	50	50
6		9-Apr	Sam's	50	50
7		12-Apr	Chevron	50	100
8		13-Apr	Sam's	50	100
9		18-Apr	Chevron	50	150
10		23-Apr	Chevron	50	200
11		10-Apr	Chevron	50	250
12	Gas Total
13	Groceries	2-Apr	Kroger	25	25
14		8-Apr	Tom Thumb	25	25
15		12-Apr	Kroger	25	50
16		15-Apr	Kroger	25	75
17		22-Apr	Tom Thumb	25	50
18	Groceries Total
19	Grand Total
20
21
22
23
Table

 

[cyrilbrd]

Oh... 2003. I am not sure if this will work. Hopefully someone with 2003 will pitch in.

Is your raw date somehow like this?

Category	Vendor	Date	Amount	Total
Gas            	Chevron	7-Apr	50	50
Gas           	Sam's	9-Apr	50	100
Gas            	Chevron	10-Apr	50	150
Gas           	Chevron	12-Apr	50	200
Gas            	Sam's	13-Apr	50	250
Gas           	Chevron	18-Apr	50	300
Gas           	Chevron	23-Apr	50	350
Groceries	Kroger	2-Apr	25	25
Groceries	Tom Thumb	8-Apr	25	50
Groceries	Kroger	12-Apr	25	75
Groceries	Kroger	15-Apr	25	100
Groceries	Tom Thumb	22-Apr	25	125
Insurance		6-Apr	120	120

I created a pivot from this and it gave this:

Category/date	Sum of Amount	Sum of Total
Gas	350	1400
7-Apr	50	50
9-Apr	50	100
10-Apr	50	150
12-Apr	50	200
13-Apr	50	250
18-Apr	50	300
23-Apr	50	350
Groceries	125	375
2-Apr	25	25
8-Apr	25	50
12-Apr	25	75
15-Apr	25	100
22-Apr	25	125
Insurance	120	120
6-Apr	120	120
Grand Total	595	1895

But I am not sure if this is what you wanted to see.

 

[txdore]

Yes, that's the data.
Can you add the vendor (should coordinate with the date and amount) to that pivot table without the running total going wacky?

 

[cyrilbrd]

Tx, if in this order: Category / Date / Vendor?

Category/date	Sum of Amount	Sum of Total
Gas	350	1400
7-Apr	50	50
Chevron	50	50
9-Apr	50	100
Sam's	50	100
10-Apr	50	150
Chevron	50	150
12-Apr	50	200
Chevron	50	200
13-Apr	50	250
Sam's	50	250
18-Apr	50	300
Chevron	50	300
23-Apr	50	350
Chevron	50	350
Groceries	125	375
2-Apr	25	25
Kroger	25	25
8-Apr	25	50
Tom Thumb	25	50
12-Apr	25	75
Kroger	25	75
15-Apr	25	100
Kroger	25	100
22-Apr	25	125
Tom Thumb	25	125
Insurance	120	120
6-Apr	120	120
(blank)	120	120
Grand Total	595	1895

I still get the data correct, but it becomes a bit difficult to read because of the "subtotals".

If the order is changed to Category / Vendor / date

it would give this:

Category/date	Sum of Amount	Sum of Total
Gas	350	1400
Chevron	250	1050
7-Apr	50	50
10-Apr	50	150
12-Apr	50	200
18-Apr	50	300
23-Apr	50	350
Sam's	100	350
9-Apr	50	100
13-Apr	50	250
Groceries	125	375
Kroger	75	200
2-Apr	25	25
12-Apr	25	75
15-Apr	25	100
Tom Thumb	50	175
8-Apr	25	50
22-Apr	25	125
Insurance	120	120
(blank)	120	120
6-Apr	120	120
Grand Total	595	1895

mean to say it would fetch the equivalence of the Total instead of computing the running total.

 

[cyrilbrd]

But you could do this:

Category	Date	Vendor	Sum of Amount	Sum of Total
Gas	7-Apr	Chevron	50	50
	9-Apr	Sam's	50	100
	10-Apr	Chevron	50	150
	12-Apr	Chevron	50	200
	13-Apr	Sam's	50	250
	18-Apr	Chevron	50	300
	23-Apr	Chevron	50	350
Gas Total			350	1400
Groceries	2-Apr	Kroger	25	25
	8-Apr	Tom Thumb	25	50
	12-Apr	Kroger	25	75
	15-Apr	Kroger	25	100
	22-Apr	Tom Thumb	25	125
Groceries Total			125	375
Insurance	6-Apr	(blank)	120	120
Insurance Total			120	120

 

[txdore]

But you could do this:

Category	Date	Vendor	Sum of Amount	Sum of Total
Gas	7-Apr	Chevron	50	50
	9-Apr	Sam's	50	100
	10-Apr	Chevron	50	150
	12-Apr	Chevron	50	200
	13-Apr	Sam's	50	250
	18-Apr	Chevron	50	300
	23-Apr	Chevron	50	350
Gas Total			350	1400
Groceries	2-Apr	Kroger	25	25
	8-Apr	Tom Thumb	25	50
	12-Apr	Kroger	25	75
	15-Apr	Kroger	25	100
	22-Apr	Tom Thumb	25	125
Groceries Total			125	375
Insurance	6-Apr	(blank)	120	120
Insurance Total			120	120

That's it! For some reason it wouldn't do that for me.
What fields did you have in the column, row, data fields? What base did you use? Can you attach the file?

 

[cyrilbrd]

Yeah sure here is the file saved as a 97-2004 file.

Link to your file.

Let me know if it works.

I placed in Row Labels:
Category then Date then Vendor.
Values remain in Column Labels
Sum of Amount and Sum of Total are placed in ∑ Values.

Finally I set the pivot as a Tabular Layout.