Pivot Chart from table - sort on non-graphed column?

[sophie619]

Hello there, this seems like it shouldn't be too tough, but I'm not coming up with anything in my search.

I asked this in another forum a couple of days ago and have not received any responses: Pivot Chart from table - sort on non-graphed column?

We have many sales people in 4 different regions. They have base salary and commission, the sum of which is their total pay.

Column headings are as follows:
names - region - base - commission - total pay
Pivot table shows
Names - base - commission - total pay (based on a selected region)

We want to filter by region, sort by total pay (high to low), and just show the graph for those sales people (maybe top 10 or 20 or whatever in each region). Graph should be a stacked bar chart with just the base and commission (b/c when they are stacked, they add to the total). However, when I try to do this, it tries to also graph the total pay column as another portion of the total in the stack.

Is this something that can't be done with a pivot table/chart? Would it be easier to use VBA to accomplish this?

Appreciate any help.

 

[RoryA]

If base, commission and total pay are separate fields, then you can't do it with a pivot chart - they always plot all fields. If Base and Commission were items in a 'Pay Type' field, then you should be able to do it because the pivot chart wouldn't plot row totals even if they were displayed in the table.

 

[sophie619]

RoryA,

Is there a way to sort on the row totals before plotting?

 

[RoryA]

Yes, you can specify that as a sort order for the Name field and also filter it to show Top 10/20/however many automatically.

 

[sophie619]

thanks!