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!
[TABLE="width: 1477"]
<tbody>[TR]
[TD][/TD]
[TD]



[/TD]
[TD]Group
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Division
[/TD]
[TD]Department
[/TD]
[TD]Group a
[/TD]
[TD]Group b
[/TD]
[TD]Group c
[/TD]
[TD]Group d
[/TD]
[TD]combined (calculated)
[/TD]
[TD]Current Total
[/TD]
[TD]Current Total %
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]real Division
[/TD]
[TD] Department of A
[/TD]
[TD][/TD]
[TD]$460,209.05
[/TD]
[TD]$167,089.22
[/TD]
[TD]$779,985.18
[/TD]
[TD]$1,407,283.45
[/TD]
[TD]$14,733,796.00
[/TD]
[TD]10%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of b
[/TD]
[TD][/TD]
[TD]$27,009.50
[/TD]
[TD]$382,315.95
[/TD]
[TD]$17,002.00
[/TD]
[TD]$426,327.45
[/TD]
[TD]$8,548,117.00
[/TD]
[TD]5%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of c
[/TD]
[TD][/TD]
[TD]$180,897.29
[/TD]
[TD]$146,180.58
[/TD]
[TD]$400,578.81
[/TD]
[TD]$727,656.68
[/TD]
[TD]$8,684,934.00
[/TD]
[TD]8%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of d
[/TD]
[TD][/TD]
[TD]$21,498.12
[/TD]
[TD]$71,302.05
[/TD]
[TD]$8,455.34
[/TD]
[TD]$101,255.51
[/TD]
[TD]$1,298,203.00
[/TD]
[TD]8%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of e
[/TD]
[TD]$436,989.81
[/TD]
[TD]$959,562.31
[/TD]
[TD]$1,173,978.11
[/TD]
[TD]$242,896.19
[/TD]
[TD]$2,813,426.42
[/TD]
[TD]$41,372,923.00
[/TD]
[TD]7%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of f
[/TD]
[TD][/TD]
[TD]$121,582.37
[/TD]
[TD]$36,649.52
[/TD]
[TD][/TD]
[TD]$158,231.89
[/TD]
[TD]$2,866,946.00
[/TD]
[TD]6%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of g
[/TD]
[TD][/TD]
[TD]$264,887.44
[/TD]
[TD]$111,513.39
[/TD]
[TD]$70,620.69
[/TD]
[TD]$447,021.52
[/TD]
[TD]$4,580,289.00
[/TD]
[TD]10%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of h
[/TD]
[TD][/TD]
[TD]$126,354.46
[/TD]
[TD]$27,558.99
[/TD]
[TD]$565,460.30
[/TD]
[TD]$719,373.75
[/TD]
[TD]$8,145,702.00
[/TD]
[TD]9%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of i
[/TD]
[TD]$1,440,981.47
[/TD]
[TD]$197,322.47
[/TD]
[TD]$341,015.31
[/TD]
[TD]$9,467.84
[/TD]
[TD]$1,988,787.09
[/TD]
[TD]$10,887,524.00
[/TD]
[TD]18%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of j
[/TD]
[TD][/TD]
[TD]$67,145.92
[/TD]
[TD]$219,473.20
[/TD]
[TD]$35,698.10
[/TD]
[TD]$322,317.22
[/TD]
[TD]$4,442,528.00
[/TD]
[TD]7%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of k
[/TD]
[TD][/TD]
[TD]$8,630.45
[/TD]
[TD]$30,819.64
[/TD]
[TD]$13,756.00
[/TD]
[TD]$53,206.09
[/TD]
[TD]$1,412,482.00
[/TD]
[TD]4%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of l
[/TD]
[TD][/TD]
[TD]$244,372.67
[/TD]
[TD]$156,557.91
[/TD]
[TD]$1,152,358.30
[/TD]
[TD]$1,553,288.88
[/TD]
[TD]$10,154,441.00
[/TD]
[TD]15%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of m
[/TD]
[TD][/TD]
[TD]$51,448.75
[/TD]
[TD]$88,451.55
[/TD]
[TD]$26,584.73
[/TD]
[TD]$166,485.03
[/TD]
[TD]$7,159,917.00
[/TD]
[TD]2%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of n
[/TD]
[TD][/TD]
[TD]$173,170.25
[/TD]
[TD]$592,663.36
[/TD]
[TD]$33,624.56
[/TD]
[TD]$799,458.17
[/TD]
[TD]$13,752,182.00
[/TD]
[TD]6%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of o
[/TD]
[TD][/TD]
[TD]$9,073.09
[/TD]
[TD]$6,385.50
[/TD]
[TD]$15,252.00
[/TD]
[TD]$30,710.59
[/TD]
[TD]$889,144.00
[/TD]
[TD]3%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of p
[/TD]
[TD][/TD]
[TD]$35,497.64
[/TD]
[TD]$163,438.13
[/TD]
[TD][/TD]
[TD]$198,935.77
[/TD]
[TD]$3,595,992.00
[/TD]
[TD]6%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of q
[/TD]
[TD][/TD]
[TD]$210,123.31
[/TD]
[TD]$462,708.02
[/TD]
[TD]$371,513.17
[/TD]
[TD]$1,044,344.50
[/TD]
[TD]$11,154,772.00
[/TD]
[TD]9%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of r
[/TD]
[TD][/TD]
[TD]$52,634.06
[/TD]
[TD]$29,307.48
[/TD]
[TD]$138,477.71
[/TD]
[TD]$220,419.25
[/TD]
[TD]$1,506,751.00
[/TD]
[TD]15%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Department of s
[/TD]
[TD][/TD]
[TD]$368,586.96
[/TD]
[TD]$163,562.24
[/TD]
[TD]$401,220.60
[/TD]
[TD]$933,369.80
[/TD]
[TD]$19,957,435.00
[/TD]
[TD]5%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD] Department of t
[/TD]
[TD][/TD]
[TD]$34,218.44
[/TD]
[TD][/TD]
[TD]$4,318.04
[/TD]
[TD]$38,536.48
[/TD]
[TD]$500,206.02
[/TD]
[TD]

8%

<tbody>
</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]real Division Total
[/TD]
[TD]
[/TD]
[TD]$1,877,971.28
[/TD]
[TD]$3,614,224.55
[/TD]
[TD]$4,370,970.15
[/TD]
[TD]$4,287,269.56
[/TD]
[TD]$14,150,435.54
[/TD]
[TD]$175,644,284.02
[/TD]
[TD]160%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NON-real Division
[/TD]
[TD]department of u
[/TD]
[TD][/TD]
[TD]$372,447.01
[/TD]
[TD]$64,952.54
[/TD]
[TD]$431,998.94
[/TD]
[TD]$869,398.49
[/TD]
[TD]$25,845,975.92
[/TD]
[TD]3%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Department v
[/TD]
[TD]$2,226,304.17
[/TD]
[TD]$74,897.21
[/TD]
[TD]$105,058.10
[/TD]
[TD]$70,935.69
[/TD]
[TD]$2,477,195.17
[/TD]
[TD]$8,408,390.43
[/TD]
[TD]29%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Department w
[/TD]
[TD][/TD]
[TD]$72,775.37
[/TD]
[TD]$156,016.97
[/TD]
[TD]$20,325.67
[/TD]
[TD]$249,118.01
[/TD]
[TD]$7,523,773.17
[/TD]
[TD]

3%

<tbody>
</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NON-real Division Total
[/TD]
[TD]
[/TD]
[TD]$2,226,304.17
[/TD]
[TD]$520,119.59
[/TD]
[TD]$326,027.61
[/TD]
[TD]$523,260.30
[/TD]
[TD]$3,595,711.67
[/TD]
[TD]$41,778,139.52
[/TD]
[TD]36%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD]
[/TD]
[TD]$4,104,275.45
[/TD]
[TD]$4,134,344.14
[/TD]
[TD]$4,696,997.76
[/TD]
[TD]$4,810,529.86
[/TD]
[TD]$17,746,147.21
[/TD]
[TD]$217,422,423.54
[/TD]
[TD]196%
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.