Mission Impossible ? Pivot Table Grand Total SubCategory

[mrchonginhk]

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

???

 

[Fazza]

Hi,

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

HTH, Fazza

 

[mrchonginhk]

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.

 

[mrchonginhk]

ie desired display:-


Apple - US: 100
Apple - Europe: 200

Orange - US 50
Orange - Europe 20

Total - US 150
Total - Europe 220
Total - 370

 

[Fazza]

Maybe easier to have two pivot tables, then?

 

[mrchonginhk]

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 ?

 

[Fazza]

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?

 

[mrchonginhk]

On same sheet is highly desirable.

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

 

[Fazza]

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.)

 

[cornflakegirl]

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?