Running total in pivot table

[Costas]

Hi,

I've created a pivot table to resemble a statement of account based on sales invoices and credit notes. I want to have a running total as another column but my running total seems to do a separate calculation for the positive and the negative amounts. The strange thing is that once I remove the Doc Type column, the running total is correct (see attached sample file). Any ideas?

Thanks
Costas

 

[Costas]

I reckon it's either too difficult to solve or it's an excel bug!

 

[cdbauer1]

It looks like Running Total In performs the running total of the primary row and then the selected Running In filter- it takes the [Date] and the [Doc Type] combinations and provides a running total of each variation.

I couldn't figure out a way around this so I tried a less elegant workaround (attached) which was to create a column adjoining the pivot table with the following formulas and conditional formatting:

Formulas
In Cell G6: =$F$6
In Cells G7:G60000: =IF($B7="Grand Total",$G6,IF($F7="","",IF($G6="","",G6+F7)))

Conditional Formatting:
Applies To: =$G$6:$G$60000
Formula: =$B6="Grand Total"
Fill (RGB): 220,230,241
Top Border (RGB): 141,180,226
Font: Bold

This will dynamically adjust the Grand Total row and will hide any rows after it while keeping a running total regardless of what the selected primary, secondary, tertiary, etc. keys are.

I'd be curious to know if anyone else had any luck!

 

[Costas]

Hi cdbauer1,

Many thanks for the fix. I guess this was the only solution.
I doubt it if anybody else had a solution as you were the only one that replied

Regards
Costas