Pivot Table % of SUB totals

pliskers

pliskers

Active Member
Joined
Sep 26, 2002
Messages
462
Office Version
  1. 2016
Platform
  1. Windows
I have a pivot table with By State subtotals, and a grand total at the bottom. I'm looking for a way to calculate each row's % of its state's total, rather than % of the grand total of the report. I want to do this WITHOUT inserting any SUMIF formulas in the data (which I know is my fallback solution).

Is there any way to use a custom formula in a pivot table to calculate % of each subtotal within a given field? Again, without adding a full field of formulas within the data source.

Thanks,
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Hi, Jeff.

In the absence of some sample data I've assumed the next row field down from 'state' is 'region' and the field being summed is 'value'.

Without adding a full field of formulas to the source data, or even another table with the state totals (I wonder if that approach would be OK for you?), but just going straight to the result. For simplicity of description, I've assumed the source data is named 'source' with a non-dynamic named range. Open a new workbook (separate from the saved data file to avoid memory leak problems.) At the first step of the pivot table wizard take the external data source option. Continue via 'get data' and choose the source data file and then the named range. Continue until you have the option to edit in MS Query. Via the 'SQL' button change the SQL - it is just text - to
Code: 
SELECT A.state, A.region, A.value, A.value/C.MySubTotal AS [Fraction]
FROM source A,
(SELECT state, Sum(value) AS [MySubTotal]
FROM source B
GROUP BY state) C
WHERE A.state = C.state
Enter this new SQL. (OK to any messages about not being graphically representable.) Hit the 'open door' icon to exit MS Query & complete the pivot table. If you like, this final worksheet can be moved into the source data file if you want the pivot table in the same file as the data. HTH, Fazza
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Showing details of Pivot table from Grand total, including Row filtering
Replies
0
Views
164
Francois2France
F
j3andc
  • Question Question
% of Grand total within a pivot table
Replies
2
Views
529
SanjayGMusafir
SanjayGMusafir
S
  • Question Question
Simply current workload. Pivot table?
Replies
0
Views
159
ShadowRider
S
O
  • Question Question
Pivot Total subtotal 2 of 3 columns only
Replies
0
Views
364
Obwan
O
S
  • Question Question
Pivot Table sum/count question.
Replies
1
Views
766
denzo36
D

Forum statistics

Threads
1,223,927
Messages
6,175,434
Members
452,641
Latest member
Arcaila

Share this page

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