T
Tony Morse
Hi All,
I have the following scenario:
This is happening in both Excel 2003 & Excel 2007 using XP.
I am accessing an external data source though ODBC.
There is one table with Account Number, Date, Month, Amount.
There is another table with Account Number, Account Name.
I join the two tables using MS Query during the Pivot Table Wizard steps.
I then create the Pivot Table with Account # & Account Name as Row
Labels, Month as Column Labels, and Amount as the Data.
Everything works fine, the Amounts for each month are summed and show up
under the correct Month for each Account, for example:
Month
Acct# Title Data 1 2 3 4 5
12345 Cash Amount 100 100 200 300 400
54321 A/R Amount 250 250 250 250 250
56789 Sales Amount 1000 1000 1000 1000 1000
The problem comes when I want to add a field for the % of Sales. I
create a new sum for Amount, then use Field Settings to Show Values as,
select % Of, select the Account # for the Base field, and select the
Account # for the Base Item - Account (56789).
The % for the Sales account is 100% as expected, but for all of the
other accounts I get a #N/A error. For Example:
Month
Acct# Title Data 1 2 3 4 5
12345 Cash Amount 100 100 200 300 400
%ofSls #N/A #N/A #N/A #N/A #N/A
54321 A/R Amount 250 250 250 250 250
%ofSls #N/A #N/A #N/A #N/A #N/A
56789 Sales Amount 1000 1000 1000 1000 1000
%ofSls 100% 100% 100% 100% 100%
If I remove the Account Name field from the Pivot Table the #N/A goes
away, and the correct % Amounts appear. If I add the field back, the
#N/A appears again.
Can anyone shed any light as to why this is happening? I was thinking
about joining the two tables to create a single table before I import
the data, but I don't want to perform this extra step if I can avoid it,
as this would create a table of static data that would need to be
refreshed often.
I have the following scenario:
This is happening in both Excel 2003 & Excel 2007 using XP.
I am accessing an external data source though ODBC.
There is one table with Account Number, Date, Month, Amount.
There is another table with Account Number, Account Name.
I join the two tables using MS Query during the Pivot Table Wizard steps.
I then create the Pivot Table with Account # & Account Name as Row
Labels, Month as Column Labels, and Amount as the Data.
Everything works fine, the Amounts for each month are summed and show up
under the correct Month for each Account, for example:
Month
Acct# Title Data 1 2 3 4 5
12345 Cash Amount 100 100 200 300 400
54321 A/R Amount 250 250 250 250 250
56789 Sales Amount 1000 1000 1000 1000 1000
The problem comes when I want to add a field for the % of Sales. I
create a new sum for Amount, then use Field Settings to Show Values as,
select % Of, select the Account # for the Base field, and select the
Account # for the Base Item - Account (56789).
The % for the Sales account is 100% as expected, but for all of the
other accounts I get a #N/A error. For Example:
Month
Acct# Title Data 1 2 3 4 5
12345 Cash Amount 100 100 200 300 400
%ofSls #N/A #N/A #N/A #N/A #N/A
54321 A/R Amount 250 250 250 250 250
%ofSls #N/A #N/A #N/A #N/A #N/A
56789 Sales Amount 1000 1000 1000 1000 1000
%ofSls 100% 100% 100% 100% 100%
If I remove the Account Name field from the Pivot Table the #N/A goes
away, and the correct % Amounts appear. If I add the field back, the
#N/A appears again.
Can anyone shed any light as to why this is happening? I was thinking
about joining the two tables to create a single table before I import
the data, but I don't want to perform this extra step if I can avoid it,
as this would create a table of static data that would need to be
refreshed often.