Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables | Excel Discussion (Misc queries) | |||
Macro for Pivot Tables | Excel Discussion (Misc queries) | |||
Product Function in Pivot Tables from Multiple Consolidation Range | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) | |||
Total two pivot tables into one | Excel Worksheet Functions |