Mission Impossible ? Pivot Table Grand Total SubCategory

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
I have a Pivot Table With 2 Row field:-

1st Column = Product - Apple and Orange
2nd column = Region - US and Europe

Now Pivot Table look like this

Apple - US: 100
Apple - Europe: 200

Orange - US 50
Orange - Europe 20

Total - 370


How to show instead of Total - 370
Show:-

Total - US 150
Total - Europe 220
Master Total - 370

???
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi,

Not sure exactly what you want. I think if you remove the product field it will show how you want.

HTH, Fazza
 
Upvote 0

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
I need to show Product field in the data area.

But I also need the Product Total to split into region. Default PivotTable total does not give breakdown on "Total Product" into region.
 
Upvote 0

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
ie desired display:-


Apple - US: 100
Apple - Europe: 200

Orange - US 50
Orange - Europe 20

Total - US 150
Total - Europe 220
Total - 370
 
Upvote 0

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Maybe easier to have two pivot tables, then?
 
Upvote 0

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
This sounds good. However, when the upper PT is refreshed and row added. they 2 lose position.

How to make sure 2nd PT always follows immediately below 1st PT to make it looks better without falling apart ?
 
Upvote 0

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Probably best to have one pivot table per worksheet. Avoids such problems. So, for two pivot tables, two worksheets.

Do you need pivot tables or might some other aproach be OK?
 
Upvote 0

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
On same sheet is highly desirable.

So there is no setting in Pivot Table option to show this format ?
 
Upvote 0

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Yes, no setting for that.

There might be other ways though. Just depends...

What about if you had a button on the worksheet to toggle between two different displays for the one pivot table?

Or if you don't need the dynamic flexibility of a pivot table, have a different approach altogether? (So, no pivot table/s.)
 
Upvote 0

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
This may be a stupid question, but I feel I have to ask: if you need to subtotal by region rather than product, why not just swap round the region and product fields in the pivot table?
 
Upvote 0

Forum statistics

Threads
1,190,794
Messages
5,982,956
Members
439,808
Latest member
agutosay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top