Keep Row Grand Total with Slicer

BloodyBill

New Member
Joined
Oct 5, 2020
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I have a pivot table with many columns. Imagine 50 salespeople (not the 4 shown). I have row grand totals.

1625246003275.png


I'd like to compare Bill's sales (10 apples) against the row total (18 apples) side-by-side.

I could hide the other columns, which preserves the calculation of the grand total. The problem with that is that it is clunky and inconvenient with so many salespeople/columns.

1625246138974.png


I'd rather just click Bill on a slicer, but that removes everyone else's sales numbers from the grand total. Now it looks like Bill sales of 10 apples were the same as the total sold (instead of 18).

1625246200789.png


Is there a way I can...?
  • keep a grand total column with an actual grand total
  • and use a slicer to conveniently hide everyone but Bill

I'm wondering if there's a solution that uses a helper column separate from the pivot itself?

Thanks,
Bill
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
This is a good time for a "Calculated Item" in the pivot table.

1) Click on cell B4 (really any name in the header row)
2) Go to PivotTable Analyze --> Fields, Items, and Sets --> Calculated Item
3) Name your new field "Total" or similar, and set the formula = Bill + Bob + John + Tom + ... (This is tedious, but you only have to set this up once)
4) Your new Total should appear in your pivot table, but note that the "Grand Total" should now be removed as it double counts this total
5) With your slicer, select both "Bob" and the new "Total" field you have set up.
 
Upvote 0
Solution
This is a good time for a "Calculated Item" in the pivot table.

1) Click on cell B4 (really any name in the header row)
2) Go to PivotTable Analyze --> Fields, Items, and Sets --> Calculated Item
3) Name your new field "Total" or similar, and set the formula = Bill + Bob + John + Tom + ... (This is tedious, but you only have to set this up once)
4) Your new Total should appear in your pivot table, but note that the "Grand Total" should now be removed as it double counts this total
5) With your slicer, select both "Bob" and the new "Total" field you have set up.
This is great...except I run into a character limit. Too many people with long names.
BTW, if I figure a way around this (and I might, by using last names) I will have to remember to amend the formula as people come and go. Right?
 
Upvote 0
This is great...except I run into a character limit. Too many people with long names.
BTW, if I figure a way around this (and I might, by using last names) I will have to remember to amend the formula as people come and go. Right?
EDIT: No, still too many characters with just last names.
 
Upvote 0
Glad that worked for you.

You're correct about having to amend the formula as new people are added. One check you could do there is to (presumably in a separate "check" pivot table that is set up where it's source is "another pivot table" so they share the same cache) compare the sum of the pieces against the grand total/2. If the sum of the pieces (including the "Total" calculated item) doesn't equal the Grand Total/2 (because of the "Total" double count), you know you need to change the "Total" calculation because it's missing a newly added person.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top