• 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.

Calculated Field for PivotTable

J

jason

Member
I wouldn't be surprised if the answer isn't right under my nose...


I have a PivotTable (the end... lol.. just kidding). the PivotTbl has a list of various departments and in the 'Values' field/box it is adding the total number of Pages for each Department.


What I'm trying to do is also count how many 'records' are being included in the Sum/total. Ideally, if i could drag the Pages field down to the Values box and "SUM' the numbers and then also Drage it again and have it "COUNT" the numbers but thats not possible so i think this may be my easiest solution


suggestions?!


oh, and i know i could add a help column to my data that would provide this as a separate field (i'm already doing that for a different field in a diff pivot) but i have several fields that i would need to do this for and 1000's of row of data; so in the name of keeping minimizing the bloatiness of my file i'd rather not go that route.
 
Debraj, thank you for the response.


But due to work restrictions all uploading sites (that i've seen used on here) appear to be blocked... so i'll try to explain


I track our Quality Complaints. Each QC should have a department assigned, who found it, when, yadda yadda, what type of product was affected and how many (this is what im after!) So lets say 10 QC's all for the same Department. 7 of them have a # of Pages affected total and 3 do not (could be because that particular job/QC did not involve a print job, thus no pages.) Right now, i have the field containing this Total Pgs amount in my "values" section of my pivot table to give me a total sum of all affected pages for said department. with me?! good.


what i also need to know is how many QC's is included in this total. so there may be 10 all together, but only 7 of them contained a number for pgs affected... so if the Total Pgs Affected sum returns 10,0000 pgs, the count should be 7 (per our little example). still with me?


So my idea would be to create a calculated field that would count the number of records that have a value > 0. what i dont want to do is create a individual helper col to preform this count if > 0 operation pre-pivot (this is a huge workbook, and i'd have to have 6 helper cols. (my example is very water-downed)
 
Hi, jason!


Just passing by and read... and I'm afraid it didn't help a lot. If due to your actual site firewall corporate policies you can't access shared files websites, well, you had a week to copy your workbook to a pendrive if not attaching it to an email to yourself and post it here.


Despite of this, and paraphrasing Hui, you're an 18-month user, you should yet know that you may post (properly embedded within backticks) a few lines of each worksheet, specify cell formulas, named ranges, manual output desired -if applicable-, and any VBA involved.


Regards!
 
You must log in or register to reply here.
Back
Top