# Calculated Item / Field in a Pivot Table

1. ## 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. ## 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

3. ## 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.

PS I hope teh attachment worked!

Cheers

Derek

4. ## 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.

5. ## 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. ## 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. ## 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. ## 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?

9. ## 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

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

#### 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