Add total to pivot table

[pto160]

I have an Account Receivable report with columns for current, 30, 60, 90 and 120 days. I created a pivot table based off this report. I am trying to get the grand total for the customer. I'm not sure if I need to add this to my source data or can I do this within the pivot table. Here is the source data.

Book1
	A	B	C	D	E	F	G
1	Cust	Order	Current	30 Days	60 Days	90 Days	>120 Days
2	ABC	3900	100
3	ABC	3901		200
4	ABC	3902			300
5	ABC	3903				400
6	ABC	3904					500
7	DEF	3905				600
8	DEF	3906		700
Sheet1

Here is my pivot table.

Row Labels	Sum of Current	Sum of 30 Days	Sum of 60 Days	Sum of 90 Days	Sum of >120 Days
ABC
3900	100​
3901		200​
3902			300​
3903				400​
3904					500​
ABC Total	100​	200​	300​	400​	500​
DEF
3905				600​
3906		700​
DEF Total		700​		600​
Grand Total	100​	900​	300​	1000​	500​

Here is what I want my pivot table to look like:

Book1
	A	B	C	D	E	F	G
1	Row Labels	Total	Sum of Current	Sum of 30 Days	Sum of 60 Days	Sum of 90 Days	Sum of >120 Days
2	ABC
3	3900		100
4	3901			200
5	3902				300
6	3903					400
7	3904						500
8	ABC Total	1500	100	200	300	400	500
9	DEF
10	3905					600
11	3906			700
12	DEF Total	1300		700		600
13	Grand Total	2800	100	900	300	1000	500
Sheet1

Cell Formulas
Range	Formula
B8,B12:B13	B8	=SUM(C8:G8)

Is this possible?

 

[sandy666]

like this?

 

[pto160]

That's what the current pivot table looks like. How do you get the total of Current +30 days + 60 days + 90days+ 120 days and put it on the customer total line? Do I need to change my source data or can I add a column on the pivot table for the total column?

 

[sandy666]

add Calculated Field
SUM(Current,'30 Days','60 Days','90 Days','>120 Days')

 

[pto160]

Thanks. I didn't know about calculated field. I have always used calculated item, which didn't work for this.
What is the difference between calculated field and calculated item?

 

[sandy666]

Calculate values in a PivotTable - Microsoft Support

Use different ways to calculate values in calculated fields in a PivotTable report in Excel.

support.microsoft.com

or

Excel Pivot Table Calculated Field vs Calculated Item

Compare Excel pivot table calculated field and calculated item. How they work, when to use, limits, warnings, examples. Step by step video, Excel workbooks

www.contextures.com

 

[pto160]

Great. Thanks for the link. How were you able to show the order as a blank cell on the Sum of Total column above the 1500 & 1300?

 

[sandy666]

use Conditional Formatting with =RIGHT(J4,5)<>"Total" and white font for last column selection
J4 = ABC - first top left cell in PT

 

[pto160]

Great. Thanks that really helped.
Also how do you get the customer to be on the same row as the order?

 

[sandy666]

PivotTable Tools - Design - Report Layout - Show in Tabular Form (any place in the Pivot Table must be selected)