Subtotals for pivot table with calcuations

S

scot72001

New Member
Joined
Nov 28, 2013
Messages
4
I believe this is not possible (as i am getting an error saying so) but does anyone have a workaround for getting monthly subtotals on a pivot table where there are calculated items?

i've tried two ways:
- highlighted date in the pivot and clicking field settings and then custom and then sum. i get an error telling me that subtotals not allowed with calculated items.
- creating a second field for the date - month and putting it beside the date in the pivot table. The issue i am having here is that for each month of the year it is putting every date beside it even if the field is blank. So for November i am getting dates from October, September.... but the data is blank - because they are not part of that month. If i can get rid of these blanks this would be a solution. Anyone have any ideas?

Thanks
Michael
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Andrew Poulsom said:
Can you post a small but representative sample of your data and what's in the pivot table?
Click to expand...

In sample below i want to pivot date on rows with test and test 2 both on columns and i want to, lets say have test/test2 as a calc field. i then want to get a monthly subtotal for test and test2.
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 71"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]29/11/2013[/TD]
[/TR]
[TR]
[TD="align: right"]30/11/2013[/TD]
[/TR]
[TR]
[TD="align: right"]01/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]02/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]03/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]04/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]05/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]06/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]07/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]08/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]09/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]10/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]11/12/2013[/TD]
[/TR]
[TR]
[TD="align: right"]12/12/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]November[/TD]
[/TR]
[TR]
[TD]November[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
[TR]
[TD]December[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]test[/TD]
[/TR]
[TR]
[TD]test[/TD]
[/TR]
[TR]
[TD]test[/TD]
[/TR]
[TR]
[TD]test[/TD]
[/TR]
[TR]
[TD]test[/TD]
[/TR]
[TR]
[TD]test[/TD]
[/TR]
[TR]
[TD]test[/TD]
[/TR]
[TR]
[TD]test
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]test2[/TD]
[/TR]
[TR]
[TD]test2[/TD]
[/TR]
[TR]
[TD]test2[/TD]
[/TR]
[TR]
[TD]test2[/TD]
[/TR]
[TR]
[TD]test2[/TD]
[/TR]
[TR]
[TD]test2[/TD]
[/TR]
[TR]
[TD]test2[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]6723[/TD]
[/TR]
[TR]
[TD="align: right"]231[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]423[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]423[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]423[/TD]
[/TR]
[TR]
[TD="align: right"]423[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
That would require a calculated item rather than a calculated field. And you can't have a calculated item if the field is grouped, sorry.
 
Upvote 0
Andrew Poulsom said:
That would require a calculated item rather than a calculated field. And you can't have a calculated item if the field is grouped, sorry.
Click to expand...

sorry yes that is what i meant a calculated item which is what i'm using. thanks for your help anyway. its surprising its restricted with this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Simply current workload. Pivot table?
Replies
0
Views
114
ShadowRider
S
M
  • Question Question
Pivot Table Calculated Field Average and Std Deviation (control chart)
Replies
4
Views
151
Sergius
S
M
  • Solved
Calculated item issue
Replies
6
Views
322
Mcstefan
M
M
  • Question Question
Pivot Table Calculated Item Crashing Workbook
Replies
1
Views
340
cooleomter
C
R
  • Solved
XLOOKUP/VLOOKUP based on Pivot Table Response
Replies
6
Views
315
rachel06
R

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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