I have a pivot table that looks something like this:

I would like to display the 'Grand Total' column at the end so that it is separated by year, like this:



Is there a pivot table setting that would allow it to display this way? I've searched high and low but haven't found anything. I appreciate any help. Thanks!

• Moved by Lanlan HuangMicrosoft contingent staff 4 hours 59 minutes ago

Hello,

There is an option PivotTableTools --> Fields,Items,&Sets --> Create Set Based on Column Items.. You can create set based on your requirements.Hope this helps.

You can add a calculated item to the pivot table to display grand totals by year:

1.  First, turn off grand totals for rows and columns (PIVOTTABLE TOOLS > DESIGN tab > Layout group > Grand Totals > Off for Rows and Columns)

2.  Click on the Price List (Invoice) field in the pivot table (hidden above the Member field?).  You must click on the pivot table cell containing the field name for the entries "Member" and "Non-Member".

3.  Add the calculated item (PIVOTTABLE TOOLS > ANALYZE tab > Calculations group > Fields, Items & Sets > Calculated item...

4.  In the "Name:" box, type: Grand Totals

5.  In the "Formula:" box, type: = Member + 'Non-Member'

6.  Click the "Add" button, and then click "OK"

• Edited by Mark18991230 13 hours 42 minutes ago

I tried and tried but I got this error message:

I tried everything... I moved fields around, removed filters, got rid of running totals, etc. This error is stubborn. Any ideas? Thanks for helping me with this.

I see that option but it is grayed out. It doesn't seem to be clickable no matter where I click. Thoughts? I appreciate the help.

It appears that your pivot table was created from OLAP source data, and calculated fields and items are disabled for this type.

Please read:

https://social.technet.microsoft.com/Forums/sharepoint/en-US/39aa92e1-0d41-4331-8b4f-5268c2f1b442/why-is-calculate-field-greyed-out-when-editing-pivot-table-in-excel-2010?forum=excel

and

http://support.microsoft.com/en-us/kb/234700

If you can download the actual data from your OLAP source into Microsoft Excel, you should be able to re-create the pivot table.

Excel 2010 with free PowerPivot Add-In.
Compatible with Office 2013 Pro Plus.
Option will be grayed out if you are using PowerPivot.
But PowerPivot will do want you want.
http://www.mediafire.com/view/ow4mmnmz8c4b4uj/03_30_15.xlsx