Pivot Table % of Subtotal

**darvistor** · 09-08-2010, 01:05 PM

Hello all.

I am trying to get some help with a data file I was asked to look at. Initially I created a user form and wrote some VBA code to filter the data set by operator, date and class and give me a tally. I also added some additional calculations to tell me for each operator in a given quarter what % of their counts were non-zero values. This worked perfectly for quarterly reports, but now they would like to see the data updated in real time.

So..... I decided to try my luck with using a pivot table. I have never really worked with pivot tables so it took me a bit to understand how they worked. I have created the pivot table using a named range so that the pivot table would udpate as I added more data. I also added some vba code so that when I add new data and select the pivot table tab the data will refresh.

The part I am having trouble with is getting the % to work. For each operator, the count can be 0 or another number. I want to know what the % of non zero values are for any given operator when there are non-zero values present. The % of row and % of column options do not give me the result I am looking for since I just get a % of the Grand total. I searched forums and google to look for solutions and didnt find anything I could get to work. I would like to avoid having to add additional formulas to the data set if possible so that I dont have the issue of recalculating as there are over 40000 rows of data in the the complete data set.

Any help would be great.

Just added the Excel file, but I had to zip the file to get the upload to work.

Thanks
E

**darvistor** · 09-09-2010, 11:18 AM

Just got the data file added. Any help now would be appreciated. :-)

**darvistor** · 09-10-2010, 08:50 AM

Ok, I uploaded my latest file. I was able to add some columns outside of the pivot table to determine the % subtotal. The formulas give me the % of subtotal that I was looking for but only if the pivot table stays in the existing format. As soon as data is added or if the table is filtered the formulas dont work as the cell reference has changed. I have tried playing around with the offset and match functions to dynamically build my formula, but I am stuck.

Anyone know how I can tweak my formula for % of Subtotal so that it is smart enough to pick up on the changes to the pivot table?

**darvistor** · 09-13-2010, 10:55 AM

Thanks for the help everyone. I figured it out myself and it is now working very well.

**larryh18** · 11-29-2011, 05:28 PM

how di you figure out what to do?

**darkyam** · 11-29-2011, 06:23 PM

larryh18, welcome to the forum. If you have a question on this topic, please post it in a new thread, in accordance with rule #2. You may post a link back to this thread if you feel it is particularly relevant. Thanks!