Solved

Pivot Table Formula and column sum

Posted on 2014-01-25
4
1,118 Views
Last Modified: 2014-01-25
In a Pivot Table suppose a calculated field C = A * B
How can I get a proper grand total of the field?
In the Grand Total row it produces SUM(A)*SUM(B) which is totally :) meaningless
But what is needed is SUM(C)
Regards
Brian
0
Comment
Question by:canesbr
  • 2
  • 2
4 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 39809538
Unfortunately, this behavior of Calculated Fields in the Grand Total row is by design. It may not be the design you would like, but it is the design that Microsoft chose to implement.

As a workaround, if you add the C = A * B formula to the raw data (before creating a PivotTable), then it will sum C exactly as you expect.

Alternatively, if you use a SUMPRODUCT formula outside of the PivotTable, it too will work as you expect.
=SUMPRODUCT((A column =A)*(B colmn = B), Column being summed)
Note that when building the formula, you should type a cell address for A and B rather than clicking on the cells. Otherwise, Excel will use a GetPivotData function reference, which may not be what you want.
0
 

Author Comment

by:canesbr
ID: 39809552
Thank you, (I too would prefer to do it all using cell formulas) but my question was to find out how to do this purely in the PT. Does the "totalling" always follow the formula for the field?
If you are, for example, doing ratios (or %ages) and you have a PT formula field C=B/A then the "total" of Total(C)=Sum(B)/Sum(A) will be correct.
But in my OP example Total(C)=SUM(A)*SUM(B) is just wrong. The case in point is a simple quantity * price.
Ought there not to be a way to specify how you want "Totals" of calculated fields to work?
Regards
Brian
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39809560
Brian,
There you go again, applying logic where logic was not invited. It won't end well.

:-)

I don't know if you have seen Microsoft Excel MVP Debra Dalgleish' discussion of calculated fields, but the problem you are describing is one she covers in detail--with exactly the same result as you describe. There is no setting that allows you to specify how you want the Total of a calculated field to be determined. Excel applies the same approach to the Total cell as it does to a cell in a Pivot row. http://www.contextures.com/excel-pivot-table-calculated-field.html
0
 

Author Comment

by:canesbr
ID: 39809583
Now I remember why I hate Pivot Tables.
Regards
Brian
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now