Please help! pivot tables

[sherobot]

I can't seem to get an answer. I think it must be very simple, but i don't
know it.
I have a list of salepeople...200. I need to show the top ten plus the grand
total and then I need to show the rest of 190 salespeople and their totals
all in then same pivot table. Please help!... or let me know if it's not
possible.
Thanks.

 

[Ed Ferrero]

Hi sherobot,

Place salesperson in the row area, sales in the data area.

Right click on the salesperson heading, click on Field Settings -> Advanced,
Sort Descending using field Sum of Sales.

Now select the first ten salespersons, right-click and select Group.
Select all other salespersons, right-click and select Group.

Right-click the Group heading, click on Field Settings, make sure the
subtotals are set on Automatic.

Note that you can select the group names (Group 1, Group 2) and enter a more
meaningful name in the cell.

Ed Ferrero
http://www.edferrero.com

 

[sherobot]

Oh my gosh..so simple...thank you Ed you're a genius!!!

 

[sherobot]

Hey Ed, I'm actually having a problem with the grouping and sorting. I
realize I am add more to the original question, but I thought it would work.
I have a Regions category i.e. CA, NY and an Industry category i.e. retail,
construction, wholesale. Initially what you said works, but when you
actually change the pivot table, i.e. I switch to CA and the retail industry
it doesn't sort correctly. For example it does this:

Retail
group 1 - top three sales people
Jim smith $12,000
Joe Doe $ 8,000
Jane Green $ 3,000

group 2 - "All Others"
mary mary $ 900
greg street $ 600
Bob Toe $ 300

But when I sort construction I get:
group 1 -Top three sales people
Jim smith $ 5,000
Jane Green $ 2,000

group 2 - All Others
mary mary $ 4,500
greg street $ 200
Bob Toe $ 100

But Mary Mary should now be part of group one. What am I doing wrong. Thanks

 

[Ed Ferrero]

When you filter the Pivot Table by an industry category. The groups are
still based on Sum of Sales for the unfiltered data. So the sort is for the
unfiltered data (ie total of all industry categories).

I don't know how to group by filtered data.

Ed Ferrero