Pivot Table - % of overall total not in table

pliskers

Active Member
Joined
Sep 26, 2002
Messages
443
Office Version
  1. 2016
Platform
  1. Windows
Here's a pivot table problem that has me stumped. It's not even easy to explain, but here goes. It has to do with a % Share calculation I'm trying to build into a report, but I want the % to be based on a Grand Total that is not reflected in the table after filtering with a slicer. Here's what I mean:

Consider that you have numerous categories of products, and individual items within those categories. I want to be able to, with a slicer, select a single Category and show the items within that category, and I want to see each item's % share not of the category, but of the grand total of all items within all categories - in other words, % of a value that is filtered out of the table by the slicer. So, the sum of the %'s will not total 100%, they'll total whatever share of ALL products that category represents - but without all categories displayed in the table.

Hope I've made it clear - is it possible to construct something in the pivot table, or add a data field that can accomplish this?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,176,103
Messages
5,901,398
Members
434,890
Latest member
creativimama

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
Top