Hi, I am creating a Pivot Table (from a table) and then trying to add a running total column into the PT. I am adding a new Value field and then showing value as a 'Running Total of..'
Although the process seems fairly straightforward, I cannot get it to work and suspect it may have something got do with my source data.
See attached spreadsheet of my attempt. Thanks for any help in advance.
Hopefully someone will improve on this.
The idea comes from the video in this tutorial: https://www.pivot-table.com/2013/07/...s-at-year-end/
The added 'TransNum' column on the 'Data' sheet is populated using:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
It looks as though that helper column does the job, i'll test it properly when back at work and let you know.
Works perfectly, thank you.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
Hi, apologies for re-opening this thread but the running total no longer works! I am using your helper column and don't believe I have made any changes that would affect it's function. See attached.
Any ideas on what's happened? Could the refresh of the SQL query cause problems?
Aaron
My feeling is that adding the Description and Transaction Date columns to the pivot table is what produces the issue. In the video (@2:10) it talks about the reason for adding the helper column to the source data and using it in the pivot table is to prevent grouping. I believe that adding the Description and Transaction Date columns reintroduces grouping. I did come up with a partial work around which is to include the Transaction Date in the values field, summarized as a sum with the number format of date.
I feel that if there is more that one Cost Center in the actual data keeping that Rows field of the pivot table will produce the same issue, although if it is a numeric value (as in the file attached to post #1) it could be placed in the values field and summarized as a Sum.
Sorry not to be of more help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks