+ Reply to Thread
Results 1 to 4 of 4

Filter report in pivot table with "greater than" and "less than"

  1. #1
    Registered User
    Join Date
    12-28-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    6

    Filter report in pivot table with "greater than" and "less than"

    Hi to everybody, I own the Italian version of Excel 2010, so I'll try to translate things, I hope you'll understand.

    I have a table which stores my expenses. The columns headers are: DATE - AMOUNT - CATEGORY. So for example a row can be "01/01/2012, -30, Restaurant" if I pay for something, or "01/01/2012, 1000, Monthly Wage" if I get money for something.

    From this table I've created a Pivot Table with:
    - DATE, grouped for years and months, as row label
    - CATEGORY, as column label
    - SUM of AMOUNT, in values label

    That's working perfectly fine.

    Now, what I'd like to do, is to create a filter to display either the expenses (where AMOUNT < 0, because I've paid for that), or the incomes (where AMOUNT > 0, because I get money).
    I tried to drop AMOUNT in the "report filter label" (I mean the 4th kind of field in a pivot table, not row, not column, not value). But when I try to filter the table using this filter, I only get the possibility to include or exclude a single amount, I mean to filter "by hand" all the possible values the AMOUNT column has. Clearly that's ok for a short table, but in case of thousands of values that's impossible to do. So, I'd like to create a filter to create two sub-pivot table: 1) a pivot table which summarises only the row where AMOUNT < 0; 2) a pivot table which summarises only the row where AMOUNT > 0.

    How could I do that? I hope I made myself clear.

    Thank you very much.

    Greetings

  2. #2
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: Filter report in pivot table with "greater than" and "less than"

    Hello Gygabyte, Welcome to the forum. I don’t think this is the perfect solution for you but my way out would be:
    Add column D and add a formula “IF(C2>0,”Income”,”Expense”) copy it all the way down and change the data source of pivot table adding column D. Refresh the Pivot table an you will have one more field which can be used in rows or columns.
    Thanks/VKS

  3. #3
    Registered User
    Join Date
    12-28-2012
    Location
    Italy
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Filter report in pivot table with "greater than" and "less than"

    Thank you a lot VKS, it worked

    I have a question then: since this new column D is just a "temporary" field which is used only in the pivot table, I tried to do the same thing adding an "evaluated field" (sorry if that's not the correct translation, I mean the custom fields with formulas that you can add to a pivot table) with exactly the same formula (only with AMOUNT instead of C2); the new field works, but if I try to drop it in the "filter report label" of the pivot table, it cannot be dropped anywhere except in the "values" label. Am I doing something wrong? I thought that would have been a great idea...

    Thank you again

  4. #4
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: Filter report in pivot table with "greater than" and "less than"

    Hi, Glad that you liked the Idea. I think you are refering to calculated field and calculated item in the Pivot Table. You can use that but calculated item and fields have their limitations in being used outside values.

    If you are happy with my suggestion then please click on the star at bottom left of my first post it will add to my reputation here.

+ 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