Solved

Pivot Table Formula and column sum

Posted on 2014-01-25
4
1,138 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 81

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 81

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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,…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

912 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