Pivot Table Formulas Help

R

RandomWa1k

New Member
Joined
Mar 25, 2009
Messages
10
Hello,

I'm working on a pivot table where I'm using the following formula: X-1

However, for the subtotal lines, I'm getting the subtotal minus one rather than the total of the results, so that the subtotal of X-1 doesn't match the sum of the lines in X.

Any ideas?

Total
X
Subtotal=10 Subtotal-1=9
A 5 4
B 1 0
c 1 0
D 3 2

Thanks!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Hi,

Maybe you're currently using a calculated field? Instead, create a new field using SQL. Such as, if you start from a separate workbook from the data (and it will be a fraction simpler, through not necessary, to give the source data a defined name before starting) at the first step of the pivot table wizard take the external data option. Follow the wizard. Right at the end take the option to edit in MS Query. When that opens hit the 'SQL' icon and edit the SQL to what you need. Complete the pivot table via the 'open door' icon. SQL will be like below. If you want, move the resultant worksheet containing the pivot table into the source data file. If you search old posts, I've posted many times on similar questions. HTH, Fazza

Code: 
SELECT X, X-1 AS [Y]
FROM your_data
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Subtotal by Date (a little different than normal)
Replies
3
Views
190
PeteWright
PeteWright
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
223
NoSparks
N
L
  • Question
Pivot table subtotals - help
Replies
3
Views
416
awoohaw
awoohaw
G
  • Question
Sum a range of a nested index match
Replies
3
Views
594
Peter_SSs
Peter_SSs
2Ruff4U
  • Solved
Summarize Duplicates in Range and Keep All Column Headings
2
Replies
12
Views
457
alansidman
A

Forum statistics

Threads
1,214,935
Messages
6,122,337
Members
449,077
Latest member
Jocksteriom

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