• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2963
  • Last Modified:

Pivot Table – Calculated field as % of running totals – Excel 2003

I am trying to insert a field into a pivot table calculated from two cumulative sums.  I can't find how to create this calculated % based on cumulative values rather than the data solely for the row.

I have attached an example workbook.

I want to add the CumReturnRate (Cumulative return rate %) into the pivot table.  Can anyone explain how to do this?

Thanks in advance.

Sanitised-Data-Table.xls
Pivot01.jpg
0
LBGUC01
Asked:
LBGUC01
  • 9
  • 5
  • 3
2 Solutions
 
Anthony MellorChartered AccountantCommented:
this looks like a "Calculated Field" question.
Pivot Table Menu:
Pivot Table / Formulas / Calculated Field with an entry of "Dispatched / Returned " and format to %

I have had a go but only column 1 has the right answer - I am posting the file in case it is my lack of understanding the formula that is at my fault whereas the principle of the calculated field will help.

You can see in the file my check data below the Table - I am pondering why the calc does not work (possibly as it is a sum of percents I suspect, just have to prove it and see if can improve it.) And I have a feeling I might just be demonstrating the problem>

Anthony

Sanitised-Data-Table-CRR.xls
0
 
Anthony MellorChartered AccountantCommented:
How to Display Data as Percentage of Total in Pivot Table

http://support.microsoft.com/kb/106359

and the key info is this:

# Double-click the Sum of Sales field button in the Data area, to open the PivotTable Field dialog box.
# Click Options and in the Show Data As list, click % Of Row, and then click OK. (If your data headings were in columns instead of rows, you would click % Of Column.)

Anthony
0
 
Anthony MellorChartered AccountantCommented:
ok I'm not 100% sure that works; busy trying to create a proof and struggling because % of this total isn't the same as % of another total.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Patrick MatthewsCommented:
LBGUC01,

I suspect that what you want to do cannot be calculated within the PT as a Calculated Field; that functionality is just not set up to divide two running totals.

What I was able to do was add formulas to the data source that generated the cumulative totals, and then I could create a Calculated Field that accurately captured the cumulative return rate.

Please see the attached file.

Also, please delete your question from yesterday, if you have not already done so, as this is a duplicate.

Patrick


Q-26010455.xls
0
 
Anthony MellorChartered AccountantCommented:
incidentally, if this data does not need to be dynamic, a straightforward (non pivot) table might suffice and then an appended column or row using simple arithmetic would work....
0
 
LBGUC01Author Commented:
Thanks for your help on this guys.

Unfortunately, it looks like you've hit the same wall I have.  The data is dynamic so a regular table won't suffice and I don't have control of the external data table, so adding columns for cumulative values will be difficult (though I am exploring it as a possibility).

Someone suggested to me that there may be a way of getting the cumulative % in there by directly editing the VB code for the table.  I've no experience of doing this so am unsure how feasible that is as a solution.

@Patrick - I closed my previous question yesterday but I think it takes 4 days unless the people who commented approve the closure.  I don't think I did a good job of phrasing my question initially; it was my first time after all. :o)
0
 
Anthony MellorChartered AccountantCommented:
ok, desperate measures: one of my more outlandish ideas was to apply a (V)LOOKUP to the pivot table itself. Is there a max limit (is that tortology?) to the possible length of the pivot table?

Variation to that is a copy and paste of pivot table result to make it fixed, but getting very messy - although I suppose the VB mavens may be able to do something with that.

I'll just have a quick play with this and come back.

.. don't like brick walls :-)

Anthony
0
 
Anthony MellorChartered AccountantCommented:
the attached does what I said above: but (a) I've just fixed the lookup range, to see if it works at all. (b) could do with some larger test data (more dynamic results).

What I am thinking here is to keep the range below the pivot table blank (good practice anyway) and then create a lookup range larger than the max possible dynamic output, then create in a fresh sheet (or above the pivot table) a report using simple lookups into the pivot table - the pivot table output may or may not "break" the VLOOKUP syntax.

Anthony
Sanitised-Data-Table-VLOOKUP.xls
0
 
Anthony MellorChartered AccountantCommented:
I am of course relying on the pivot table not being pivoted...
0
 
Patrick MatthewsCommented:
>>Someone suggested to me that there may be a way of getting the cumulative % in there by directly
>>editing the VB code for the table.  I've no experience of doing this so am unsure how feasible that is
>>as a solution.

Maybe.  What is the code that fetches the data?
0
 
LBGUC01Author Commented:
VLOOKUP is a possibility.  Are you saying it could be put in a formula within the PT?  The main issue with a look up is that my real data has many different values for ‘Area’.  So the lookup_value need to be assigned dynamically.

I’m not sure what CB code is used to produce the PT at present.  There was none written especially.  I think they were suggesting I start on using:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End SubPrivate Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
0
 
Anthony MellorChartered AccountantCommented:
VLOOKUP: I wasn't thinking of applying it inside the PT and multiple area output would stop it;  have to give it some more thought if you wanted to explore that way.

I think Patrick's suggestion above about adding a helper column would be better than recreating the PT with lookups to add a row of calcs. Could you load the entire dataset into a sheet (or indeed file) for pre-work?

How much data is there? How many rows and columns?

Anthony
0
 
LBGUC01Author Commented:
Apologies for the delay in replying.  I've was out of office yesterday.

The point of going with a pivot table was to have an automatically refreshing report.  Pre-work would probably negate that benefit.  The entire dataset is approx 50 columns and 8,000 rows - a new row being added every working day.  The data itself is being pulled directly from a SQL database.  It's looking like I'm going to have to schmoose the data owners to try and get a cumulative column added their end.

Thanks for your help on this guys, I can't believe something so simple isn't part of the base functionality!

PS: Am I allowed to award points for effort rather than resolution?.. :o)
0
 
Anthony MellorChartered AccountantCommented:
ok, how about this: sounds like the historic dataset is fixed no changes.
Create a sheet and add a helper column like Patrick suggests and

add one row each day - so what does a row look like?

we could consider how to add that row of data, methinks some VB code could pickup and append that row automatically - into a LIST would also give you lots more simple query possibilities if that were a bonus.

8.000 rows isn't so much in modern Excel.

In fact the in LIST MANAGER the helper column could be a CALCULATED column which helps with confidence regarding spreadsheet error control. That process does have a special input row which can accept pasted data. VB may even be overkill; depends how automated you want to be.

We try :-)

Anthony
0
 
Patrick MatthewsCommented:
broomee9,

Thanks for your Cleanup efforts :)

In my opinion, http:#a31770274 answered the original question.  If Anthony feels he answered the question
too, I have no objection to a split.

Patrick
0
 
LBGUC01Author Commented:
Broomee9,

Apologies.  This is my first EE question and have not got myself familiar with the etiquette yet.  It does look like 'you can't do it' is the correct answer but I wasn't sure I should accept that as a 'solution'. I was holding out, just in case.

I really appreciate the time the experts have taken with this, so I have decided to split the points.
0
 
LBGUC01Author Commented:
Not the positive solution i was hoping for unfortunately but the answer was accurate.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 9
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now