+ Reply to Thread
Results 1 to 5 of 5

Difference between Averages in a Pivot Table

  1. #1
    jbesr1230@hotmail.com
    Guest

    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


  2. #2
    Myrna Larson
    Guest

    Re: Difference between Averages in a Pivot Table

    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, jbesr1230@hotmail.com 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



  3. #3
    jbesr1230@hotmail.com
    Guest

    Re: Difference between Averages in a Pivot Table

    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


  4. #4
    Myrna Larson
    Guest

    Re: Difference between Averages in a Pivot Table

    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, jbesr1230@hotmail.com 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



  5. #5
    jbesr1230@hotmail.com
    Guest

    Re: Difference between Averages in a Pivot Table

    Hi Myrna,

    The Pivot Table setup is:
    Rows: Process
    Subprocess
    Data: Budg summarized by Average
    Act summarized by Average
    Columns: There are no column fields.

    Myrna Larson wrote:
    > 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, jbesr1230@hotmail.com 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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1