Pivot Table % of SUB totals

[pliskers]

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,

 

[Fazza]

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

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