Following a request for information on a discussion list on any potential pitfalls with Calculated Fields in Pivot Tables, I reminded myself of a discussion I had on this topic in 2009. What follows is that discussion in full.
You will find a related discussion in Chapter 5 of my Excel Project book, links on the home page of this site, of course.
Discussion hosted by: MS Excel General Q & A List EXCEL-G@PEACH.EASE.LSOFT.COM
Discussion started by: Duncan Williamson
Date: Fri, 30 Jan 2009
I have just hit a wall!
I am trying to analyse a fairly basic PT and as part of that process I have set up a calculated field.
- Invoice Number
- Customer Name
- Product Group
- Selling Price/unit
- Cost Price/unit
There are three calculated fields:
- total sales = selling price * quantity
- total costs = cost price * quantity
- gross profit = total sales – total costs
It works except in the case when there are two sales transactions for a product by the same sales person to the same customer with the same account number and in that case the total sales price calculated field = (sales price 1 + sales price 2) * total quantity
There is a third transaction for one of the products but for that sale, the SP/unit is different from the other two transactions and that calculation is OK.
The same applies to the cost calculated field too.
Is this clear and can anyone explain why this is happening? Moreover, what’s the work around?
Best wishes as always
From Tony Lau:
ALL three calculated fields should be included in your transactional data per invoice, not as a calculated field within the PT. This way, it avoids too many unique data items for the PT to summarise.
I did that too Tony and of course it worked well enough as you would expect.
Imagine the situation, however, where I am using an external source of data, for example a remote database file, that I cannot influence or change. I NEED a calculated field then unless I want to try to reconstruct the database.
In my case, there are only three events, two different prices … Excel 2007 can’t cope with that? The total length of the list I have been using in this case is only 20 – 30 lines long!
I should add, that as I expand the PT and add more differentiators, the problem goes away. That is, once I give the PT something to allow Excel to say transaction one is different from transaction two, it’s OK.
Why, though, does the PT add the two prices together in my calculated field before it multiplies it by the quantity?
This is a very good learning point that either I hadn’t come across before or hadn’t noticed in spite of being a veteran PT user in my cost accounting work for a couple of intensive years.
I can’t say I’ve seen this point referred to in any of the otherwise excellent Excel books and web pages that I’ve read … please correct me if you know different! It is already in the book that I am currently writing now!!
[Later, I found this: Mr Excel made this point in answer to one of his visitors in March 2002:
See the Help topic for “Syntax for calculated field and item formulas in PivotTables” where it states “Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula”.
From Jimm Johnson:
You don’t necessarily need the calculated field in the PT though. I have used one worksheet to store data queried from an external source, another worksheet to lookup that data plus additional columns with calculations, then a third worksheet with the pivot table referencing the data on worksheet No 2. Then I only needed to refresh the external data query & pivot table and good to go. I used dynamic ranges in worksheet No 2 to populate the same number of rows as what appear on the first worksheet that queries the external source.
If you’re defining the PT calculations, the PT may be executing them based on your definition. Make sure you have correct parentheses where needed to carry out the mathematical operations in the right order. As Tony indicates, you may be better off with the calculated fields in the worksheet and let the PT aggregate them.
Thanks: Many thanks again to everyone who was involved in that discussion on the Excel-G discussion list.