Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3390
  • Last Modified:

Pivot Table Total as sum When field is Max

If i have a pivot table that has a column with max values can i change the total to be the sum of the values and not the max?

i.e. I have a column showing the max values:

1000
2000
500
250
1750

The total is currently showing 2000 (the max), I need it to show 5500 (the sum)

Any ideas?
0
carlspywell
Asked:
carlspywell
  • 2
1 Solution
 
Rory ArchibaldCommented:
You can't do that unless you add the field again as a sum.
0
 
dlmilleCommented:
You could get rid of grand totals at the bottom, then create a calculated item called "Total of All" equal to max(field1) + max(field2) + max(field3) + ...

Click on the row labels, then select PivotTable ribbon element, Formulas, Calculated Item.  Then Add the new Item, and use the field list in the second listbox.  See below:
calculated item
and the resulting table looks like:
Pivot Table Result with Total of the MAX items...

See attached:

Enjoy!

Dave
Pivot-Sum-of-Max-r1.xlsx
0
 
dlmilleCommented:
@Carl - additional issues, did this solution not work for you, or are you ready to close this out?

dave
0
 
carlspywellAuthor Commented:
Pointed me in the right direction
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now