+ Reply to Thread
Results 1 to 9 of 9

Pivot table: Filter column with multiple criteria

  1. #1
    Registered User
    Join Date
    06-27-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2011
    Posts
    11

    Pivot table: Filter column with multiple criteria

    Hi,

    I hope you can help as I've searched quite a lot...and no results.

    I am looking for a way to set a pivot table filter that applies more than one criteria (or any solution that is functionaly equivalent).

    The concrete case:
    I have data (from a survey) with 4 columns:
    - Response ID
    - Organisation
    - Question
    - Score

    I am using a pivot table to summarize data and give me the average scores for each organisation.
    I need to have only the organisations with the Top 10 average scores, so I've used one of those Top 10 "Value Filters" and it works fine.

    Now, some of the organisations have a very small number of responses, so I want to exclude those from the Top 10.
    So, I would need to have two simultaneous "value filters":1) "Count of Score" Greater Than xxx AND 2) Top10 item by "average Score"

    But I can't find a way to set both criteria...either one or the other...

    Can you help me?

    cheers,
    miguel

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Pivot table: Filter column with multiple criteria

    In the PivotTable Options there is a checkbox in the totals&filter tab which "Allow multiple filters per field".

    or you can supply a sample workbook on what you would like to attain so that other's can look at it.

    thanks.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Pivot table: Filter column with multiple criteria

    Hi miguel,

    I'm using 2010 and I think it is the same as Mac 2011 in Pivot Tables. Find the attached with your answer.

    When you filter the rows and have two values (average and count) you can choose which you are filtering by. In the attached I'm showing the top 5 averages with counts greater than 4.
    I think this proves what you are wanting to do.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    06-27-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2011
    Posts
    11

    Re: Pivot table: Filter column with multiple criteria

    Quote Originally Posted by vlady View Post
    In the PivotTable Options there is a checkbox in the totals&filter tab which "Allow multiple filters per field".

    or you can supply a sample workbook on what you would like to attain so that other's can look at it.

    thanks.
    Hi Vlady,

    Thanks!


    I did find the option and set it to on...but i see no diference in the filter behaviour.
    I still can set only one. Whenever I select a new one, the old goes away.

    So I can only have Topx filter OR Count greater than... I need both at the same time.

    Yes I should have posted a sample spreadsheet...But MarvinP's is a perfect sample.

    cheers,
    miguel

  5. #5
    Registered User
    Join Date
    06-27-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2011
    Posts
    11

    Re: Pivot table: Filter column with multiple criteria

    Quote Originally Posted by MarvinP View Post
    Hi miguel,

    I'm using 2010 and I think it is the same as Mac 2011 in Pivot Tables. Find the attached with your answer.

    When you filter the rows and have two values (average and count) you can choose which you are filtering by. In the attached I'm showing the top 5 averages with counts greater than 4.
    I think this proves what you are wanting to do.
    Hi Marvin,

    Thanks a lot for your answer and sample (that I should have provided..)
    Your description and sample are perfect. I'm also using 2010 but on windows.

    However I can still only see the Top 5 filter active... I've pasted the data to another sheet and it seems that no result in the Top 5 has less than 4 answers, so you second filter wouldn't exclude any "question".

    I confess that I took longer to come back here, because I thought I was missing something competely obvious.
    And maybe I am... :-) but I can't find it.

    So, If you could using you own example sheet and set it to show the Top 5 average scores AND a count of scores greater than 6 it would be great.

    With your sample data, this double filter should display the pivot and values for the questions nr: 3, 15, 6, 5, 1
    but shouldn't display the questions nr: 2, 8, 12 because they have counts below 7.

    thanks so much for your help.

    Miguel

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Pivot table: Filter column with multiple criteria

    Hi Miguel,

    Your question reminds me of a college class I had called Algebraic Structures. We studied logic, associative and commumative properties of numbers.

    To the chase. If you do the top 5 Average and then Count 7 or more, you get a different answer than doing 7 or more first and top 5 average second. It is like you think that 2^3 = 3^2 or 4-6 = 6-4 (and they are NOT). The order that you do your top 5 average and count greater than 7 will give you different answers.

    See the attached for doing them both ways and the different answers.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-27-2012
    Location
    Portugal
    MS-Off Ver
    Excel 2011
    Posts
    11

    Re: Pivot table: Filter column with multiple criteria

    Hi Marvin,

    Thanks!

    No doubt there on commutative properties of the operations, or in this cas the lack thereof...
    We do get different results if we change the order.

    In my case I want to exclude questions with less than X answers, to that list need the top Y.

    And that's what you've shown me in your example.


    I can see that in your example you've used 2 pivot tables in sequence. One applies the first rule, than copy-paste values, than another pivot table with the second rule.

    Now, my problem is that I thought it could be done with one single pivot table and (somehow) apply both rules to the data.
    So, do you think this is possible, or not?

    The option pointed by vlady (see above) regarding the "Allow multiple filters per field", seems to hint that it is possible... but the fact is that I've activated it...and everything seems to stay the same.

    Any ideas?

    cheers,
    miguel

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Pivot table: Filter column with multiple criteria

    Hi miguel,

    Now that we've decided that the order of the filters is important....
    I'd do the pivot table that limits the count first to a place on my sheet. Then I'd create a Dynamic Named Range using the location of this first Pivot Table. Then I'd do a second Pivot Table to show the top 10 using the named range of the first pivot table.

    See http://www.gilliganondata.com/index....your-charts-2/ or
    http://dedicatedexcel.com/dynamic-na...in-excel-2010/

  9. #9
    Registered User
    Join Date
    06-15-2015
    Location
    Bratislava, Slovakia
    MS-Off Ver
    2013
    Posts
    1

    Re: Pivot table: Filter column with multiple criteria

    Hello guys,

    I work with excel 2013 and I seem not to work around this :-( The above solution from Marvin woks great however I do need to filter by two different values. I have the value of the remaining stock and the estimated number of days of sales. What I want to achieve is to see the top 10 items with the highest number of the days of sales but at the same time I only need to see the items that have the remaining stock over 5000.

    I tried the above steps and first I filtered the items with the higher value than 5000, to this point it works great, but when I then make the top 10 by the number of remaining days it looks like the first filter is overwritten.

    I would create a new row in the source data to be able to filter the above 5000 easily, but I am using an OLAP source and cannot modify the source data by adding fileds.

    I would really appreciate any help as the only solution I was able to come up with at this point is to filter the >5000, than arrange ZtoA by the number of days and hide the rows I dont want to see, but I dont really like this soulution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1