Calculated field using the Grand Total and Values fields of Pivot chart

I want to do a calculated field using the Grand Total and Values Count field of Pivot chart

How do I put the manually created formula in Column E of Pivot into the Pivot chart, or Data sheet so i can add it to Pivot chart?

I searched threads and other sites and all I found per answers.microsoft.com: You will not be able to do that with a calculated field formula. Either perform the calculation in another column of your base data or outside the Pivot Table.

Pivot-CalcSummaryFields.png

In this case, you can duplicate the number but display it as percent of row total. It's more information than you need, however.

Hi,

A few quick questions if I may
- Do you have Power Pivot?
- Can you add a couple of columns to the source data?
- Do you need the subtotals?

Pivot tables are powerful tools, but the do have limitations.

This can't be done with just a native pivot table as far as I know. There may be some tweaking that can be done with the source data using formulas in a helper column that will give you a column header that can be used in the calculation, but you mentioned that you don't want to do this.

Another method would be to use VBA to generate the formulas outside the pivot table and again, that's off the table.

VBA could be used to extract just the columns you want, which would look nice, but it would be a static report.

Power Pivot is an Excel add-on. As far as I am aware, for Excel 2013 you will either have it already available, or not be able to use it- it requires a Pro Plus or Professional Plus version of Office.

If you don't need the subtotals for ABC, DEF etc, you can do it with a simple calculated item in the pivot table. See attached.

Thanks Xlnitwit! Your solution is beautiful!

It took me a few to apply it to my actual document. I didn't realize I couldn't have another Pivot in the workbook. Every time i tried to select Calculate Item I kept getting the error: If one or more fields in the PivotTable have calculated items, no fields can be used in the data area two or more times, or in the data area and another area at the same time. If you are trying to add a field, remove the calculated items and add the field again. If you are trying to add a calculated item, change the PivotTable report so that no field is used more than once and then add the calculated item.

Pivot-Solution.png

You can have other pivot tables in the workbook, although there are limitations with calculated items as the message says. If need be, you can always use a separate cache for the other pivot tables so that the calculated item does not apply to them.