Pivot Issue, want SOME Row items values on Pivot but all in total...

This is a good one. Lets say I have a pivot that shows Location, Dept and Total Sales by month. So Location and Dept are in the row area, Period is in the column area and Total Sales are summed in the page area.

Well, they decide they do not want to see Dept: 999 on the pivot. Thats easy, just click the dropdown and uncheck it....they are amazed at my ingenuity!

Well, they then notice Dept: 999 is no longer included in the Location total or grand total....makes sesne to me I say!

Well, they then specify that Dept: 999 should NOT appear on the pivot but SHOULD be included in subtotals / totals.

I AM STUCK!!! -- not so ingenious I guess!

Any ideas!!!!!!!!

Anyone?

I think Excel is due for a mjor overhaul...and I do not mean that vista crap either...

I also need to be able to do percentages at the subtotal level.....

How about using "group by" and creating a group called "Unassigned" or similar which includes Dept 999 (and hopefully at least one other)
The totals still balance but "Unassigned" includes Dept 999 and any others you don't want to separately list.

I'll look into that!

theres no way to do it in pivot table however i think u can wiing it this way

assuming ur pivot table dat is coming from another excel sheet.

at the end of the data.. last row
add a dept called GRAND TOTAL and use the sum forumla to calculate the totals of each column in the last row

now when you go back to ur pivot table

the grand total mite be in the middle.. jus use the pivot table> order settings to move it at the very bottom

remove the grand totals from the pivot table >options

bold the grand total row.. now if you are using the subtotal option.. u mite get two rows at the bottom looking the same

jus hide one of the rows and there

ur grand totals wont change now even if u filter out any company..\

the only pain is that you have to do that last grand totals row every time u update ur raw data.. but i think will surely impress ur boss


That do the trick??

yes and no...they want to be able to manipulate the pivot having the GRAND TOTAL thus change. Example, they may want to filter on a particular location....so they would see just say location 'A', all depts except 999 but still have 999 included in the total or grand total for location A.

the GROUP thing did not work for me...now I have a group they do not wish to see, when I hide it the totals change.

The best I could come up with was to Click just to the left of Dept 999 so all 999s are highlighted in the pivot....then set the font to white so they disappear...though there is still an extra blank line, it does not look too bad since 999 was at the end......not exactly what I was hoping for but.....

I still need a way to get % of subtotal...likely can not do that either!

just hide the blank row

"I still need a way to get % of subtotal...likely can not do that either!"
- see attached .. may help