Pivot - Advance Filter

    Hi,



    I have created a Pivot table for top 3 projects and their value for
    each client within each country using Advanced options and "Autoshow"
    option.



    This works fine but I would also like to classify the other projects
    (not within the top 3) as "Others" for each client so that the total
    value per client is also visible within the pivot table.



    Is this some how possible automatically through the pivot table or do I
    mannually need to create some sort of ranking within the database which
    can be quite time consuming.



    Anyone's help on how to create the Pivot or any other solution is
    appreciated.



    Regards
    Sandip.

    Re: Pivot - Advance Filter


    I had this same issue with my pivot tables - I'm using CSV files as the datasource.If you want to be updating your Table on a regular basis I see 2 ways:


    1st method
    I wanted to group large accounts, and everyone else. Since I have a table that contains Customer + Region linked linked in, I added a column onto that that had: For top 5 customers write their names; for others write others.


    I then used that field in my pivot table rather than the standard CustomerID field. Abit long winded, but since I run macros every time I suck in new data to my pivot table, it isn't too bad.


    2nd Method
    Select every Project apart from your top 3 projects within the pivot table, right click and select Group. It will place them in group 1: You can then rename that to Others.


    Remove your main project field, and you will be left with your top 3 projects and group 1.


    The problem I see with this method is you have to regroup every time new data [specifically new projects] are added to the pivot table, but I don't know of any way other than these two.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login