PivotTable Calculated Fields - ok in Excel but not in Access

Hi, I've been using PivotTables in Excel and used the Calculated Field to show data as a % of a particular Base Field and Base Item (eg. % of Turnover, where Turnover is one of sevaral Account Types) - great !

Problem is, I need to move all this to Access which seems to do almost all the Pivot jobs Excel can do, however the Calculated Fields are different - you have to type in an expression and it's not clear what the format is . All it needs is a way to reference the Base Field and then Base Item, like Excel does.

I've tried selecting the main total which it formats as [Measures].[Total1]. Choosing the other AccountType reveals [Account Type].[Account Type] but what I really need is the Item within it, in this case Turnover. So I tried:

[Measures].[Total1] - [Account Type].[Turnover]

Note this isn't the actual calc I want but just to keep it simple and work out the format. Basically, it doesn't work because it throws error "Infinite recursion detected...".

The data calculation (if you're interested) is:

Account Type | £Actual. | % Act T'over.

Labour £24,098.65 17%
Materials £12,104.29 9%
Overheads £2,222.82 2%
TotCOS £38,425.76 27%
TotGMargin £101,577.78 73%
Turnover £140,003.54 100%

So, the "% Act Turnover" is the Calculated Field - Labour is calculated as 17% of the £140,003.54 Turnover.


So, how do you reference the Excel-like Base Field and Base Item to calculate % of Item - or isn't this supported in Access for some reason?

Hope someone can help, this is a real blackspot in Access, I'd be surprised if it can't be done and there must be an Access Pivot guru out there!

Phil