cumulative total in merged pivot table

nishpearl123

Board Regular
Joined
Jun 14, 2010
Messages
96
I have created a pivot table from date in 2 different sheets.

Both sheets only has date in common. Report is working fine.

My report has filed called - "Amount Receivble" (AR).

Now, i wish to to have AR and also a cumulative total of AR.

But I am not finding how to only insert AR again and do the cumulative total for that newly inserted field only.

pl. assist.

regards, nishith
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
On the Field Settings dialog click Options and select 'Running Total' in under 'Show data as'.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,364
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Add the Value field again and set its Options to display as Running Total in Row. The exact steps vary depending on the version of Excel you are using.
 

nishpearl123

Board Regular
Joined
Jun 14, 2010
Messages
96
Hi thanx for the immediate reply. (i use Excel 2007)

Since i have crated the date by giving 2 ranges both ranges in seperate sheets, in the pivot Table Field List, it gives only 4 fields :

1. Row : contains the common field of both the ranges - "Date"
2. Column : contains all the fields from both the ranges
3. Value
4. Page 1 : it is automatically put in the filter box (it has options to view data of 1st range or the 2nd range

But when i add the "value" field again in the valves box, it adds all the column fields again.

Further, if i add the calculated field, it shows me the 4 options as mentioned bove, instead of the entire fields on range1 and range2

Now, please guide me again, as how i can i avoid the repeatation of the all column fields?

and how can i use the caluclated filed.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,364
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
But when i add the "value" field again in the valves box, it adds all the column fields again.
That's because you now have two values for each column. You can either rearrange the data to show the two values within each column field, or you can have the columns grouped within the value field, but you have to show all columns for both values.

For calculated fields you are limited to what is shown in the formulas dialog.
Pivot tables built from consolidated ranges are extremely limited, IMO, which is why I never use them.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,943
Messages
5,508,273
Members
408,673
Latest member
CELER_

This Week's Hot Topics

Top