Pivot Table Subtotal calculations

[dkaragiannis]

Hello,

I am working on creating a report using a pivot and I can't seem to get the subtotal to calculate properly. I have broken out each departments totals by its group and I used a calculated item to create the combined field ( group a + group b + group c + group d). I need to have to calculate the percentage of the group total / current total. I also created this field by created a calculated item (combined / current total) in which I get the correct percentage until it calculates the subtotal (highlighted in red). It shows 160, 36 and 196
where I need it to show 8, 9 and 8% respectively ( 14,150,435.54 / 175,644,284.02). Any help is greatly appreciated.

Thank you!

		Group
Division	Department	Group a	Group b	Group c	Group d	combined (calculated)	Current Total	Current Total %
real Division	Department of A		$460,209.05	$167,089.22	$779,985.18	$1,407,283.45	$14,733,796.00	10%
	Department of b		$27,009.50	$382,315.95	$17,002.00	$426,327.45	$8,548,117.00	5%
	Department of c		$180,897.29	$146,180.58	$400,578.81	$727,656.68	$8,684,934.00	8%
	Department of d		$21,498.12	$71,302.05	$8,455.34	$101,255.51	$1,298,203.00	8%
	Department of e	$436,989.81	$959,562.31	$1,173,978.11	$242,896.19	$2,813,426.42	$41,372,923.00	7%
	Department of f		$121,582.37	$36,649.52		$158,231.89	$2,866,946.00	6%
	Department of g		$264,887.44	$111,513.39	$70,620.69	$447,021.52	$4,580,289.00	10%
	Department of h		$126,354.46	$27,558.99	$565,460.30	$719,373.75	$8,145,702.00	9%
	Department of i	$1,440,981.47	$197,322.47	$341,015.31	$9,467.84	$1,988,787.09	$10,887,524.00	18%
	Department of j		$67,145.92	$219,473.20	$35,698.10	$322,317.22	$4,442,528.00	7%
	Department of k		$8,630.45	$30,819.64	$13,756.00	$53,206.09	$1,412,482.00	4%
	Department of l		$244,372.67	$156,557.91	$1,152,358.30	$1,553,288.88	$10,154,441.00	15%
	Department of m		$51,448.75	$88,451.55	$26,584.73	$166,485.03	$7,159,917.00	2%
	Department of n		$173,170.25	$592,663.36	$33,624.56	$799,458.17	$13,752,182.00	6%
	Department of o		$9,073.09	$6,385.50	$15,252.00	$30,710.59	$889,144.00	3%
	Department of p		$35,497.64	$163,438.13		$198,935.77	$3,595,992.00	6%
	Department of q		$210,123.31	$462,708.02	$371,513.17	$1,044,344.50	$11,154,772.00	9%
	Department of r		$52,634.06	$29,307.48	$138,477.71	$220,419.25	$1,506,751.00	15%
	Department of s		$368,586.96	$163,562.24	$401,220.60	$933,369.80	$19,957,435.00	5%
	Department of t		$34,218.44		$4,318.04	$38,536.48	$500,206.02	8% <tbody> </tbody>
real Division Total		$1,877,971.28	$3,614,224.55	$4,370,970.15	$4,287,269.56	$14,150,435.54	$175,644,284.02	160%
NON-real Division	department of u		$372,447.01	$64,952.54	$431,998.94	$869,398.49	$25,845,975.92	3%
	Department v	$2,226,304.17	$74,897.21	$105,058.10	$70,935.69	$2,477,195.17	$8,408,390.43	29%
	Department w		$72,775.37	$156,016.97	$20,325.67	$249,118.01	$7,523,773.17	3% <tbody> </tbody>
NON-real Division Total		$2,226,304.17	$520,119.59	$326,027.61	$523,260.30	$3,595,711.67	$41,778,139.52	36%
Grand Total		$4,104,275.45	$4,134,344.14	$4,696,997.76	$4,810,529.86	$17,746,147.21	$217,422,423.54	196%

<tbody>
</tbody>

 

[mick0005]

This is a common issue with pivot table subtotals. In fact it is a known issue by Microsoft. https://support.microsoft.com/en-us/kb/211470 I don't know if they ended up fixing it in 2013 because I am still on 2010.

Sometimes what you can do to get around this is to perform the calculations in your data table itself. I can't test this easily for you without the source data table, but maybe you can give it a try.