View Full Version : Getting the counts from multiple filters to use within pivot

2015-09-23, 08:41 PM
Hi all,

This is my first post (first posts are always questions aren't they?!):o

I have a pivot of a large array of data (about 300,00 rows ). I'd like to perform an average calculation within the pivot (calculated field?) but I don't know how to determine the denominator. This would be the number of items that are getting through the filters. These are Period (1-13) Week (1-4) and Day (Sun - Sat). There could be as few as one item in there or as many as 13 x 4 x 7. The numerator is the sumof field in the pivot builder.

Could anyone explain if i can get this value and if so would I need to use it in a bit of VBA (which I could probably manage) or if it can be used within an Excel formula in a calculated field maybe.

I hope the question makes sense,

Thank you

2015-09-25, 01:15 PM
There's a built-in average function in pivot tables:

2015-09-25, 02:25 PM
Hi, thanks for the reply

I've seen the average function but I believe that it produces the average of the items along the same row. My data is arranged with shops in each of the columns and data for specific days for each shop is filtered and presented in the pivot.

What I need is the average of the data coming into the cell. So if I filter (slice) two day's data to be displayed in a pivot cell, I'd like to divide by two before displaying.
The question is how to get the number of filter items because it changes.

Specifically I could filter data for two saturdays. How can I get that and use it anyway to process the numbers before pivoting maybe?

2015-09-25, 03:29 PM
Attach the file or link to it in the cloud or send it to me privately (Private Message me here for an address to send it to) if it's sensitive. You can also do some search/replacing in the file to de-sensitise it.

2015-10-09, 08:27 PM
I couldnt figure out how to do with calculated fields, but one way(and its not very clean) is to also add count of the same field that you are doing sum on to the pivot table and then get the average