Pivot Table - Values as a % of Maximum of Parent Grouping

Mayanwolfe

New Member
Joined
Jun 5, 2013
Messages
27
I know that title sounds a bit confusing. I have various fees grouped by departments. Customers may only belong to one department, but they may pay one or more fees in their department. I have revenue by fee type, as well as the count of customers that paid each fee type. I would like to determine the % of customers that pay them within each department. The challenge is that since a customer may pay multiple fees, their records are duplicated, so I cannot use the % of Parent Grouping Total option. I would like to create a calculated field that divides the customer count for each fee type by the max customer count for that department. However, I've not been able to find a method that will give me what I'm looking for. See an example below. The far right column is the field I'm trying to create:

1589220378410.png


I can do this easily with formulas off to the right of the table, but I would like the option to change demographic filters on the pivot table, which will change the row counts for each department and mess up the formulas. Instead, a calculated field would shift appropriately with the table. Thanks for any ideas!
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,127,347
Messages
5,624,136
Members
416,012
Latest member
rockermom59

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