% of Column Total in Pivot [EXCEL 2016]

[KevyWevz]

Good day all,

Am hoping you can help me with a minor issue I'm having that's causing some big delays for me.

I have a pivot table with 2 columns(column C & D in the attached file) of numeric data and would like to be able to add a third column(column E) that provides a percentage of the data in column D against the grand total of the data in column C

Example: In the attached file, Cell D3 has 138 and cell C28 has 7923. I would like cell E3 to read 1.74% and for all table cells in column E to be provided as a % of the grand total of column C. Columns G:H have been provided as a sample.

The data feeding into the table would change often and slicers will be used with other data points to provide different view combinations we require so any help you are able to provide would be very much appreciated.

Kind regards,
Kevin

[dflak]

First, the most important question: where in Ireland are you. I have kin in Dublin and Waterford.

I did this with the help of a helper column. I put the data in an excel table because excel tables (a) know how big they are so that pivot tables built off them always reference the exact amount of data they need no matter how many rows wind up in the table and (b) Pivot tables automatically copy down formulas. In this case I made a helper column called Column 3 and gave it the formula =1*SUM([Column1]).

I had to add the "1 *" to force Excel to accept the result as a number. I'm not sure why it needed it.

Then I built the pivot table and added the following calculated variable GTOT = Column 2 / Column 3.