Error on Calc Item on Pivot Table

[dallen43]

I'm new to Pivot tables. I need to provide data to the users on sales. We have Divisions. Each division has several Departments and each department has several Classes. I want to provide the data at the class level and was hoping that the users could use the pivot tables to see the data summarized at division or department levels and let them drill down.

Anyway, what I've done, with the aid of the information in the forum, is to normalize the data. Here is an example:

Div, Dept, Class, Field, Data
1, 10, 100, Curr Sales, 110.00
1, 10, 200, Curr Sales, 220.00
1, 10, 100, Pri Sales, 100.00
1, 10, 200, Pri Sales, 200.00
etc

There are actually a few more Fields: Curr Cost, Pri Cost, Curr Disc, Pri Disc, Curr Rtns, Pri Rtns. They are all in the above format.

Then I created a pivot table which looks like the following:

Div Dept Class Curr Sales Pri Sales etc
1 10 100 110.00 100.00
1 10 200 220.00 200.00
etc

And this works great at this point.

Then I would like to add several calculated items. For example, I want to know how much the sale increased/decreased. So I go to Pivot Table, Formula, Calculated Item and create a field called Pct with the formula =(('Curr Sales'-'Pri Sales')/'Pri Sales')*100

When I click Okay, I get "There are too many records to complete the operation".

I'm using Excel 2003. There are 4025 rows on the normalized table. There are 599 rows on the Pivot Table.

This doesn't seem to be excessively large, so I don't really understand the error. Can some one help?

Deb

 

[dallen43]

In order to determine what is causing the problem, I did a test with 3 divisions, 2 departments for each division and 3 classes for each department. The data was set up as following:

Div Dept Class Fields Data
1 10 100 Curr Sales 1000
1 10 200 Curr Sales 2000
1 10 300 Curr Sales 3000
1 20 1100 Curr Sales 1100
1 20 1200 Curr Sales 2100
1 20 1300 Curr Sales 3100
2 30 2100 Curr Sales 1200
2 30 2200 Curr Sales 2200
2 30 2300 Curr Sales 3200
2 40 3100 Curr Sales 1300
2 40 3200 Curr Sales 2300
2 40 3300 Curr Sales 3300
3 50 4400 Curr Sales 1400
3 50 4500 Curr Sales 2400
3 50 4600 Curr Sales 3400
3 60 5100 Curr Sales 1500
3 60 5200 Curr Sales 2500
3 60 5300 Curr Sales 3500
1 10 100 Pri Sales 500
1 10 200 Pri Sales 520
1 10 300 Pri Sales 1500
1 20 1100 Pri Sales 600
1 20 1200 Pri Sales 900
1 20 1300 Pri Sales 1400
2 30 2100 Pri Sales 700
2 30 2200 Pri Sales 1000
2 30 2300 Pri Sales 1800
2 40 3100 Pri Sales 600
2 40 3200 Pri Sales 1100
2 40 3300 Pri Sales 1800
3 50 4400 Pri Sales 600
3 50 4500 Pri Sales 1100
3 50 4600 Pri Sales 2100
3 60 5100 Pri Sales 700
3 60 5200 Pri Sales 1200
3 60 5300 Pri Sales 1900

Then I created the pivot table, it looks as expected:

Sum of Data2 Fields
Div Dept Class Curr Sales Pri Sales
1 10 100 1,000.00 500.00
200 2,000.00 520.00
300 3,000.00 1,500.00
10 Total 6,000.00 2,520.00
20 1100 1,100.00 600.00
1200 2,100.00 900.00
1300 3,100.00 1,400.00
20 Total 6,300.00 2,900.00
1 Total 12,300.00 5,420.00
2 30 2100 1,200.00 700.00
2200 2,200.00 1,000.00
2300 3,200.00 1,800.00
30 Total 6,600.00 3,500.00
40 3100 1,300.00 600.00
3200 2,300.00 1,100.00
3300 3,300.00 1,800.00
40 Total 6,900.00 3,500.00
2 Total 13,500.00 7,000.00
3 50 4400 1,400.00 600.00
4500 2,400.00 1,100.00
4600 3,400.00 2,100.00
50 Total 7,200.00 3,800.00
60 5200 2,500.00 1,200.00
5300 3,500.00 1,900.00
5100 1,500.00 700.00
60 Total 7,500.00 3,800.00
3 Total 14,700.00 7,600.00
Grand Total 40,500.00 20,020.00

Then when I add a calculated item to get the difference between curr sales and prior sales, every class appears in each department and every department appears in each division. This changed 18 detail lines to 333 detail lines. Following is a small example:

Sum of Data2 Fields
Div Dept Class Curr Sales Pri Sales Diff
1 10 100 1,000.00 500.00 500
200 2,000.00 520.00 1480
300 3,000.00 1,500.00 1500
1100 0
1200 0
1300 0
2100 0
2200 0
2300 0
3100 0
3200 0
3300 0
4400 0
5200 0
5300 0
4500 0
4600 0
5100 0
10 Total 6,000.00 2,520.00 3480

Why is this? Is there any way to avoid this?
Should my base table be formatted differently?

Thanks in advance for your help.

 

[dallen43]

I have finally managed to resolve the issue. This solution only works with Excel 2002 or 2003 (but not 2000).

I start with a comma delimited file with the following columns:

Div, Dept, Class, Curr Sales, Pri Sales, etc.

After opening the file with excel (2002 or 2003), I select Data, Pivot Table Reports.
Step 1 of 3: Choose Microsoft Office Excel List or Database. Click Next.
Step 2 of 3: Enter the appropriate range of cells. Click Next. (Note, I previously named the range and use the name here.)
Step 3 or 3: Click Layout. Drag "Div", "Dept" and "Class" to "Row". Drag "Curr Sales" and "Pri Sales" to "Data". Click OK. Click Finished.

This creates a Pivot Table, but the "Sum of Curr Sales" is above "Sum of Pri Sales". Right click on the header named "Data" and select Order and Move to Column.

The "Sum of Curr Sales" and "Sum of Pri Sales" are now in separate columns. I may now add a calculated field as following:

Click on the Pivot Table button on the Pivot Table Toolbar.
Select Formulas and Calculated Fields.
In the Insert Calculated Field screen:
Name: Pct
Formula: = (( 'Curr Sales'- 'Pri Sales')/ 'Pri Sales')*100
Select OK.

This will not add extra rows and looks exactly the way I want. All I do now is a little formatting.

I just wanted to post the solution, in case someone else has the same problem.

Deb