calculate % of each items total in a year in Pivot Table

[aravindhan_3]

Hi,

Plz help me for getting %of each item based on total in Pivot Table.
I have attached an example file with the details

thanks for your help
Arvind

[Aflatoon]

You can not do that within the pivot table, I'm afraid. (unless you have Excel 2010) You would have to add a column to the data that calculates the percentage for each row of its parent row, I think.

[Bob Phillips]

Yes you can. If you click on the data item and select Field Options from the menu, then select Options button, then where it says Show Data As, change that to % of Column.

[Aflatoon]

Perhaps I misunderstood. It was my impression that the percentage was supposed to be the percentage of the parent row for that column (eg A and Jan), not the percentage of the entire column, which is, as you say, easy enough.

[aravindhan_3]

Hi,

Thanks for your replies.

Aflatoon: i require the result as you said.


The percentage was supposed to be the percentage of the parent row for that column (eg A and Jan), not the percentage of the entire column,

Is there any way in doing this in pivot or is there a way to do this through macro

[Jarlisle]

The only way to do it in a Pivot table is to move the Dept. to the columns and then use the % of Column in the value field settings, but this spreads the data out a lot because the SuR codes are so varied. Otherwise you would have to use formulas.