• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculatio item in Pivot Table

S

santhurcv

Member
Hi All, Can someone me in Calculatio item in Pivot Table in Pivot


Here is my Pivot look like the formula i need is Pending/Grand Total


Pending Resolved Grand Total

AOL 4 4

ARA 5 5

CLX

CRL

DHL 2 1 3

GEN 1 1


Total 2 11 13
 
Good day santhurcv


If you go to the “Calculations” part of the ribbon and from the “Field, Items and Set”, choose Calculated Field, then choose your fields in the new window
 
Great help Bobhc,


i have calculated as said,


i got the values as well, but the problem is i dont get same calculation for Grand total for the calculated Item :the error is : #DIV/0!
 
santhurcv


It would help if you uploaded a copy of the file, if you do not have a Dropbox account it only takes a few minuets and it is free
 
Hi Bobhc ..


Please find the below link where i have uploaded the and mention what is need the yellow colored thanks for you


https://www.box.com/s/9gj96krzi4k9bjzo8bcv
 
santhurv


Busy at work so have not had much time to look at the pivot table, there is a lot going on in there :).

If your wrap your code like this...=IFERROR(Resolved/(Pending+Resolved),0)...your work sheet will not drive you made with #DIV/0! and you will get the results you want.
 
Great Help Bobhc,


But this problem again which i missed to mention below is the another problem


Example :] Total Ticket where 4 but in Sub BGL Total it says 5

The same is also been show in above uploaded sample file.


Can you please help with this

[pre]
Code: 
BGL	                     Sub :BGL Total
Row Labels	Pending | Resolved	Performance
AOL		            4               100%	      5
ARA	                    5               100%	      6
[/pre]
 
santhurcv


Working with calculated fields in a pivot table in a pain in the neck and they can and do screw up your pivot so that all you are left with is a pile of crap, there are reams of internet data on how to use them but the reading of them would turn you to drink :)


To be honest if I had to use your data I would add the necessary sub and total fields in the raw data. I would also turn your raw data into a table so that any future fields or additional data would update in the pivot on refresh.

You could then construct a pivot table with the fields, subs and totals...this way you do not need calculated fields.It is far easier to add a column to the data field than to a pivot table. When I was trying to learn Access one of the first thing taught was to set out you needs on paper then construct and it is a sound piece of advice.
 
You must log in or register to reply here.
Back
Top