Calculated Field/Item in a Pivot

[Matt D Francis]

Hi,

Need help created a calculated field in a Pivot Table.

Below is an example of my Pivot including the first two rows of data.

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic
A559 13.0 9.1
A578 0.0 0.9


What I want to add for each row is a third column which displays the
difference between the two columns so I would end up with:

Average of LoS DIABETIC
OPCS_1 Diabetic Non Diabetic DIFFERENCE
A559 13.0 9.1 3.9
A578 0.0 0.9 0.9

I can do this by adding a formula in the cell to the right of the last
column of the pivot, but this is no good as the references will be thrown
out if the Pivot changes

So how do I add this calculation as part of the Pivot table so that it
updates with a refresh?

The field giving the figures for Diabetic & Non Diabetic is an Average and
this seems to be causing a problem.

 

[Roger Govier]

Hi Matt

Create a Calculated field called Difference. Use the formula
=AVERAGE(Diabetic)-AVERAGE('Non diabetic' )

 

[Matt D Francis]

Hi Roger and thanks for the quick reply. I'm afraid that didn't work though.

I got a "your formula contains an error" message and it reverted to the
Calculated Field wizard highlighting the word Non of Non Diabetic in the
formula. I typed it as you did. Is naming the field DIABETIC confusing things
as this also one of the two possible values found in that column?

Matt

 

[Roger Govier]

Hi Matt

I set myself up a very small table with your data following your first
posting with headings of OPCS, Diabetic and Non Diabetic.
I then created the PT and inserted a calculated field as described, and
all worked well.
Did you enclose the Non Diabetic in single quotes?

The formula I posted, is one that I copied (using Ctrl+C) from the pane
in the setup of Insert Field.
Try copying my formula and pasting with Ctrl+V into the setup pane. If
it worked for me, then it should for you.

 

[Matt D Francis]

Yes I tried the copy & pasting your formula including the single quotes but
get the same error.

I'm not sure the table you built is the same as mine. The source data would
look like this

Code LoS DIABETIC
X358 1.2 Diabetic
X357 2.2 Diabetic
X358 1.8 Non Diabetic
A559 3.4 Diabetic
X367 0.3 Non Diabetic
X358 0.9 Non Diabetic
A559 3.4 Diabetic

etc

So one column to flag Diabetics, as opposed to seperate Diabetic and Non
Diabetic columns. These two values (Diabetic/ Non Diabetic) or what gives me
my two columns in the Pivot.

Is that how yours was set out?

If so, I don't know what else I could have done wrong.

 

[Roger Govier]

Hi Matt
No my data was entirely wrong for you.
Using your small subset, I put Code as a Row field, Diabetic as a Column
field and Average of LoS as a Data field.
I then inserted Average of LoS a second time, clicked Advanced, and Show
data as Difference from.
I chose Diabetic as my Base Field and Diabetic as my Base item.

I think this gives the result almost as you want.
Drag the Data button to the Total area to see the columns side by side
instead of beneath each other.
I also went into Table Options and de-selected Grand Total by Row.
You end up with 5 columns, the third, being Average of LoS2 for Diabetic
(which is always empty as it is the difference from itself).
I just hid column C to not show it and have a clearer result.

I hope this helps.

 

[Matt D Francis]

OK, definitely see where you're coming from with this approach and I'm nearly
there.

I created the "% Difference From" field, but I don't follow what you mean by
"drag the Data Button to the Total Area". What is the "Total area"?

I get % values I want, but they appear under the first value:

Code Data Diabetic Non Diabetic
V481 Average of LoS 1.8 4.7
Average of LoS2 -61.23%

If there's a way of getting that % figure to appear on the same row, so as
to get 1 row per code, I don't know it, but it would be useful, I've needed
this before!

Thanks for your patience

Matt

 

[Roger Govier]

Hi Matt

I shouldn't have used the word "area".
Drag the Data Button to where the Total appears in the PT, and the data
will appear side by side.

 

[Matt D Francis]

OK I've dragged it all over the place andl am getting pretty much what I want
now, thanks a lot for your help.

Matt