Pivot Table Subtotal calculations

dkaragiannis

New Member
Joined
Apr 7, 2015
Messages
1
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>
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mick0005

Active Member
Joined
Feb 21, 2011
Messages
406
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,165
Messages
5,594,622
Members
413,918
Latest member
Mikey_C

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