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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

N
  • Solved
Sumproduct with Table Rows doesn't work
Replies
8
Views
249
Nicha
N
S
  • Question Question
Simply current workload. Pivot table?
Replies
0
Views
159
ShadowRider
S
N
  • Question Question
FormulaR1C1 wrong?
Replies
9
Views
156
RoryA
RoryA
LearningByDoing
  • Solved
GROUPBY + Slicer
Replies
5
Views
56
Fluff
Fluff
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
365
STARL0RD
S

Forum statistics

Threads
1,223,920
Messages
6,175,377
Members
452,638
Latest member
Oluwabukunmi

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