Unanswered: 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...".