• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Running total in pivot table

Costas

Costas

Member
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
 

Attachments

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!
 

Attachments

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
 
You must log in or register to reply here.
Back
Top