+ Reply to Thread
Results 1 to 6 of 6

Pivot table Calculated field issue - formula divides by the count of rows from datasource

  1. #1
    Registered User
    Join Date
    02-04-2014
    Location
    Baltimore, Maryland
    MS-Off Ver
    Excel 2010
    Posts
    17

    Pivot table Calculated field issue - formula divides by the count of rows from datasource

    Here is how my source data is setup:

    $Amt Name Biweekly Pay Amt Project biweekly pay period end date
    $100 John Smith $500 A 12/31/15
    $50 John Smith $500 A 12/31/15
    $50 John Smith $500 A 12/31/15
    $50 John Smith $500 A 12/31/15

    I am entering a calculated field formula named "%age funded" = Amount/Biweekly Pay Period Amt

    The value field setting; to summarize value field by = sum

    My calculated field is returning for %age funded = 0.125;; not 0.50 as it should be, what is wrong??
    I can tell that my calculated field formula, is dividing the actual %age funded by the count of $Amt, is there a way to fix this??

    Here is an example of the outputted pivot table:

    *row label* Name *row label* Project *row label* biweekly pay period end date *value* sum of $Amt *calculated field* %age funded
    John Smith A 12/31/15 $250 0.125

    Any and all help is appreciated
    Last edited by UMBiii; 11-18-2015 at 01:03 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pivot table Calculated field issue - formula divides by the count of rows from datasou

    Hi,

    We really need to see your workbook in order to advise with any certainty.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Pivot table Calculated field issue - formula divides by the count of rows from datasou

    250/2000 = 0.125
    This calculation is correct. Calculated field will always sum the fields before performing other operations.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Pivot table Calculated field issue - formula divides by the count of rows from datasou

    Hi,

    Do you need to do this calculation with a calculated field in the PT?
    Seems to me it would be much simpler to use the data on which the PT is based. Extract a list of unique names from column B into G2:G..whatever and in H2 copied down enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-04-2014
    Location
    Baltimore, Maryland
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Pivot table Calculated field issue - formula divides by the count of rows from datasou

    thanks, this is what I was missing;

    I kept thinking it would be the 'Biweekly pay period amt' from the pivot table, so I was thinking

    =sum(A2:A5)/$500 = 250/500 = 0.50

    I was overlooking that the calculated field is always referencing source data so it actually is =

    =sum(A2:A5)/sum(C2:C5) = 250/2000 = 0.125


    Thanks for that, it was one of those moments were I had been working too long in excel and my brain was getting fuzzy!

  6. #6
    Registered User
    Join Date
    02-04-2014
    Location
    Baltimore, Maryland
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Pivot table Calculated field issue - formula divides by the count of rows from datasou

    And to Richard;

    yes I actually ended up just doing the calculation in the source data and pivoting on that field.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Count Columns in Pivot Table as Calculated Field
    By probablyjoel in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 08-20-2015, 03:16 PM
  2. Calculated field issue in pivot table
    By disblohs in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 05-14-2014, 05:51 PM
  3. [SOLVED] Pivot Table - Calculated Field - Count and Sum
    By rtabaldi in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-14-2014, 01:10 PM
  4. Pivot Table calculated field to count if issue "needs follow up"
    By xnukemx47 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-07-2014, 06:18 PM
  5. Count in a calculated field (Pivot Table)
    By Joćo Morsa in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-18-2013, 02:47 PM
  6. Pivot Table Calculated Field Issue
    By Peeekay in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-11-2013, 10:39 AM
  7. [SOLVED] Using a Pivot Table Calculated Field to get a Unique Count
    By Mike Struckman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-22-2005, 02:40 PM

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