Difference between Averages in a Pivot Table
 

Hello,

I am wrestling with a pivot table with source data in the following
format

Process Subprocess PrtID Budg Act
ADDIMPRV CHNG-USE B04M0603 19 16.8
ADDIMPRV CHNG-USE B04M2436 21 45.3
ADDIMPRV MF/COMM B04M0281 187 76.4
ADDIMPRV MF/COMM B04M0510 110 42.65
ADDIMPRV MF/COMM B04M0878 44 27.7
ADDIMPRV MF/COMM B04M1500 79 48.55
ADDIMPRV RES B04M1348 27 22.55
ADDIMPRV RESI B04M1403 31 32.8
B-REVSN COMMRCL B04Q0214 344 240.1
COMLBIND COMLBIND B04DB001 55 19.65
CUxxxxx CUPOTHER L04CU004 50 20.25
CUxxxxx CUPOTHER L04CU008 45 18.25
CUxxxxx CUPOTHER L04CU014 50 19.75
CUxxxxx CUPTOWER L04CU002 60 31.75
STRUCT MOBILExxxx B04L0227 25 17.55
STRUCT MULT B04L0133 187 72.1
STRUCT MULT B04L0346 424 221.15
STRUCT MULT B04L0465 197 114.9
STRUCT MULT B04L1233 156 54.1
STRUCT MULT B04L1666 113 42.05
STRUCT MULT B04L1834 122 55.25

What I want to get to is a pivot table with averages:

1.Process 2.Subproc 3.AvgAct 4.AvgBud 5.AvgAct-AvgBud 6.Act/Budg%
alpha alpha Num Num Calc Calc


AvgAct and AvgBud are no problem of course. The AvgAct (-) AvgBud is
what I cannot get to. I have tried various formulae in calculated
field e.g. sum(act)/counta (PrtID) and I cannot get what I need.

Can anyone help? Also, is there a good source for explaining the
difference between calculated field versus calculated Item? and the
calculations that be done--perhaps examples?

Thanks.

JBE

I believe the 2 formulas you need are

=Act-Budg

and

=Act/Budg

Try it and see if that gives you what you want.

Debra Dalgleish's web site, www.contextures.com, has info on Pivot Tables that
may help.


On 3 Mar 2005 10:26:29 -0800, wrote:

Hello,

I am wrestling with a pivot table with source data in the following
format

Process Subprocess PrtID Budg Act
ADDIMPRV CHNG-USE B04M0603 19 16.8
ADDIMPRV CHNG-USE B04M2436 21 45.3
ADDIMPRV MF/COMM B04M0281 187 76.4
ADDIMPRV MF/COMM B04M0510 110 42.65
ADDIMPRV MF/COMM B04M0878 44 27.7
ADDIMPRV MF/COMM B04M1500 79 48.55
ADDIMPRV RES B04M1348 27 22.55
ADDIMPRV RESI B04M1403 31 32.8
B-REVSN COMMRCL B04Q0214 344 240.1
COMLBIND COMLBIND B04DB001 55 19.65
CUxxxxx CUPOTHER L04CU004 50 20.25
CUxxxxx CUPOTHER L04CU008 45 18.25
CUxxxxx CUPOTHER L04CU014 50 19.75
CUxxxxx CUPTOWER L04CU002 60 31.75
STRUCT MOBILExxxx B04L0227 25 17.55
STRUCT MULT B04L0133 187 72.1
STRUCT MULT B04L0346 424 221.15
STRUCT MULT B04L0465 197 114.9
STRUCT MULT B04L1233 156 54.1
STRUCT MULT B04L1666 113 42.05
STRUCT MULT B04L1834 122 55.25

What I want to get to is a pivot table with averages:

1.Process 2.Subproc 3.AvgAct 4.AvgBud 5.AvgAct-AvgBud 6.Act/Budg%
alpha alpha Num Num Calc Calc


AvgAct and AvgBud are no problem of course. The AvgAct (-) AvgBud is
what I cannot get to. I have tried various formulae in calculated
field e.g. sum(act)/counta (PrtID) and I cannot get what I need.

Can anyone help? Also, is there a good source for explaining the
difference between calculated field versus calculated Item? and the
calculations that be done--perhaps examples?

Thanks.

JBE

Thanks for trying Myrna. I tried that one also. =Act-Budg gives the
difference between the totals of each process-subprocess e.g. ADDIMPRV
- CHNG-USE "Act-Budg" evaluates to 22.1 not 11.05.

Anyone else have any thoughts?

Thanks

Which columns do you have in the row and column fields, and which in the data
fields?

On 3 Mar 2005 15:25:29 -0800, wrote:

Thanks for trying Myrna. I tried that one also. =Act-Budg gives the
difference between the totals of each process-subprocess e.g. ADDIMPRV
- CHNG-USE "Act-Budg" evaluates to 22.1 not 11.05.

Anyone else have any thoughts?

Thanks