Pivot Tables - % of Total Including *all* Items

Hi there,

Thanks for your help in advance! My first post, but I've been reading this site and the forums for a while now.

I should probably say that I have posted on another forum (Ozgrid, but no one has replied there for a week - and my problem is still not solved.

Basically, I have a lot of data that changes on a regular basis (daily in fact). I need to be able to generate a chart to show this data and have it update automatically. Previously this was all worked out manually.
The data involves the following. Each record has a sector (A,B,C for sake of simplicity), a country and a value. I want to show the percentage of the total that each country holds.
This is accomplished fairly easily by the % of Total function. However, when I proceed to hide the two sectors that I do not want on the charts generated (I need to show percentage of each country for each sector individually), the percentages are recalculated to be out of the total for just that sector.

The solutions I've looked into are:-
Calculated Fields - I cannot find a way of referencing the entire set of data and Summing it - or referring outside the PivotTable to a total value field.

"Include Hidden Items In Total" button - this is greyed out. I have read somewhere that it has to do with OLAP (I think) data sources? My data source is a table of data so possibly not.

Table Options > "Subtotal Hidden Page Items" - This option is checked but it has no effect.

Any help would be gratefully received, I've been stuck on this for a while now, and I need to use it at the end of the month!

(Edit: I can email an example spreadsheet to anyone if they request - or take the one from the post on Ozgrid)

If you know the categories and they do not change from day to day i would look at sumif and sumproduct to get my category values and work out the percentages from these cells, i would not go near a pivot table, this way the chart should stay the same

unless you provid emore info I can not be more specific

Regards

DAv

Excelenator - will do, was just worried about duplication. When I get back to work on Tues (Mon is a public holiday), I'll post it.

Thanks Dav, I will give you more info when I post the spreadsheet. (My mind is blank because it's late and I'm tired ... :S)

Okay, I've given up on finding an automatic solution to this.

What I have at the moment is using GETPIVOTDATA to calculate the percentages for each one outside the table, generating charts that way.

If anyone has a similar problem to this, please feel free to ask for more information.