Help to calculate % automatically in Pivot Table

**cyberuser** · 01-12-2011, 11:49 AM

Can i calculate automatically the % of a certain number into a pivot table based on each break ?

the attached file provides a better information about the question....

thanks !

**DonkeyOte** · 01-12-2011, 11:52 AM

You could opt to display Sum of People as % of Parent Row Total

Note: this would affect your Subtotals in so far as they too would become % of their Parent Total - so you might opt to show Sum of People twice over (once showing as Sum and the other as the aforementioned %)

**cyberuser** · 01-12-2011, 12:22 PM

I've checked this option... but i was only able to see % for the row ... which return all values as 100% or the % for the column, which return % for all values ...

any idea to break by each city ?

**DonkeyOte** · 01-12-2011, 12:27 PM

Would help if we knew the version ... you might find it simplest to move City to Column Field (from Row) and then use % of Column.
(assumes you have less Cities than unique Dates else you could reverse and use % of Row)

**cyberuser** · 01-12-2011, 12:36 PM

the version is 2003... and the data is around 20.000 records to process... so there will be a lot of differences for this column.

**leo73pk** · 01-12-2011, 12:55 PM

@ cyberuser,

may be you are looking for this.

**DonkeyOte** · 01-12-2011, 12:55 PM

Originally Posted by D.O

...you might find it simplest to move City to Column Field (from Row) and then use % of Column.

Originally Posted by cyberuser

...there will be a lot of differences for this column.

If you're saying you have too many unique cities for a Column Field set up then I confess I'm not sure there's a way to do what you want within the (2003) PT itself - that said I'm no expert on Calculated Fields etc...

Adding additional info to your data sheet would be the logical next step, however, given the sample data (lack of order), suggested volume of data (20k) and version that would be pretty inefficient

To elaborate on the additional info. route - using your sample file:

Please Login or Register  to view this content.

You can then add %Alloc to your PT (set to SUM)

This won't be quick... it would be better to sort the source data by City & Date and then your calculations would be significantly faster (instantaneous)