Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Pallet1210A
 
Posts: n/a
Default Percent of total in Pivot tables

I have a pivot table where I am showing 2 company locations (e.g. London,
Manchester), both split into different departments (e.g. Admin, Sales, IT,
Management). I want to show each department as a percentage of the total IN
EACH LOCATION. By using % of total, I only seem to get the percentage of the
GRAND TOTAL. Can someone tell me how to get aroung this. Example below
showing numbers first, then what I get, then what I want.

Many thanks
London Sales 2
Admin 8
Manchester Sales 7
Admin 3
Grand Total 20

London Sales 10% (This shows as 10% of the GRAND TOTAL staff
numbers)
Admin 40% (ditto)
Manchester Sales 35% (ditto)
Admin 15% (ditto)
Grand Total 100%

London Sales 20% (i.e. 20% of the total for London only)
Admin 80% (i.e. 80% of the total for London only)
Manchester Sales 70% (i.e. 70% of the total for Manchester only)
Admin 30% (i.e. 30% of the total for Manchester only)
Grand Total 100% (don't need the grand total)
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

Move Department to the row area of the pivot table.
Then, change the Percentage field to % of Row, instead of % of Total.

Pallet1210A wrote:
I have a pivot table where I am showing 2 company locations (e.g. London,
Manchester), both split into different departments (e.g. Admin, Sales, IT,
Management). I want to show each department as a percentage of the total IN
EACH LOCATION. By using % of total, I only seem to get the percentage of the
GRAND TOTAL. Can someone tell me how to get aroung this. Example below
showing numbers first, then what I get, then what I want.

Many thanks
London Sales 2
Admin 8
Manchester Sales 7
Admin 3
Grand Total 20

London Sales 10% (This shows as 10% of the GRAND TOTAL staff
numbers)
Admin 40% (ditto)
Manchester Sales 35% (ditto)
Admin 15% (ditto)
Grand Total 100%

London Sales 20% (i.e. 20% of the total for London only)
Admin 80% (i.e. 80% of the total for London only)
Manchester Sales 70% (i.e. 70% of the total for Manchester only)
Admin 30% (i.e. 30% of the total for Manchester only)
Grand Total 100% (don't need the grand total)



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot tables Gus Excel Discussion (Misc queries) 1 August 31st 05 08:31 PM
Macro for Pivot Tables Thomas Excel Discussion (Misc queries) 1 March 15th 05 02:03 AM
Product Function in Pivot Tables from Multiple Consolidation Range bbishop222 Excel Worksheet Functions 0 February 22nd 05 05:55 PM
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. stvermont Excel Discussion (Misc queries) 1 February 17th 05 02:34 AM
Total two pivot tables into one Os Excel Worksheet Functions 4 February 1st 05 08:01 PM


All times are GMT +1. The time now is 10:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"