Results 1 to 5 of 5

Thread: Getting the counts from multiple filters to use within pivot

  1. #1

    Getting the counts from multiple filters to use within pivot



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all,

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

    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

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    There's a built-in average function in pivot tables:
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capture.JPG 
Views:	45 
Size:	29.1 KB 
ID:	3960  

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

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    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.

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •