+ Reply to Thread
Results 1 to 9 of 9

Calculated Item / Field in a Pivot Table

  1. #1
    Registered User
    Join Date
    01-29-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Calculated Item / Field in a Pivot Table

    Hi everyone

    I don't seem to be able to acheive the calculation I need in a pivot table. I need the 'Liability' total divided by the 'Count of Branch' but I keep getting the 'Divided by Zero' error. I have tried changing numeric values to count also but that just calculates the total numeric value that sits behind. What am I doing wrong? Any tips will be greatly appreciated.

    Values
    Row Labels Count of Branch TOTAL LIMITS Sum of Field3
    FIDELITY COMM 7,597 3,655,800,000 #DIV/0!
    FIDELITY FIN 375 25,014,500 #DIV/0!
    MLC 9,169 27,463,660,000 #DIV/0!
    MLF 375 68,785,500 #DIV/0!
    PI 5 7,500,000 #DIV/0!
    Grand Total 17,521 31,220,760,000 #DIV/0!


    Cheers

    Derek

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,689

    Re: Calculated Item / Field in a Pivot Table

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    01-29-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Calculated Item / Field in a Pivot Table

    Hi Sixthsense

    Thank you for your reply and apologies for my delayed response. I have attached a desensitised source version of what I am trying to achieve. Essentially I want to divide the 'Sum of Liability' with the 'Count of Type' in the pivot table but I am having no luck.

    Thanks in advance for any help you can offer.

    PS I hope teh attachment worked!

    Cheers

    Derek
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    Re: Calculated Item / Field in a Pivot Table

    Hi Derek,

    I think you need to do a calculation to the right of the PT, in your case. See the attached.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    01-29-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Calculated Item / Field in a Pivot Table

    Hi MarvinP

    Thanks for your reply. The actual pivot data is much larger in the full version and the filters on the pivot will be frequently used so I really need, if possible, for the calculation to be within the pivot - if there is a way!!

    Cheers

    Derek

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    Re: Calculated Item / Field in a Pivot Table

    I've been looking for one of these for a few years and have failed. It seems so easy but calculations are done with each record not from the totals or counts in the PT.

    I thought I was close to an answer when I saw there was a difference between a calculated item and calculated field.
    See http://www.databison.com/calculated-...a-pivot-table/ or http://www.contextures.com/excelpivo...ulateditem.htm
    but I'm not sure either will do what you want.

  7. #7
    Registered User
    Join Date
    01-29-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Calculated Item / Field in a Pivot Table

    You are absolutley correct - if you are able to change from Sum to Count etc then the calculated item or field shoud follow however as you pointed out iy calculates from the record itself - frustrating! It seems I am unable to find a way around it.

    Cheers

    Derek

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

    Re: Calculated Item / Field in a Pivot Table

    This looks like simple average to me - may you not add Liability field again and change function to Average?
    • 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

  9. #9
    Registered User
    Join Date
    01-29-2014
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Calculated Item / Field in a Pivot Table

    That works perfectly, thanks!! I was trying all these complex methods but it is often the simplest that works best...

    Thank you very much Izandol, much appreciated.

    Cheers

    Derek

+ 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. Add Sequence as a calculated field or item in pivot table
    By roymunoz03 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-04-2013, 03:09 AM
  2. Replies: 1
    Last Post: 12-21-2012, 05:40 AM
  3. Replies: 2
    Last Post: 11-05-2012, 11:32 AM
  4. Replies: 3
    Last Post: 06-06-2006, 12:10 AM
  5. Pivot Table Calculated field item
    By Tim in forum Excel General
    Replies: 1
    Last Post: 09-23-2005, 08:05 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