Calculated Field/Item in a Pivot

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.

Hi Matt

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

--
Regards

Roger Govier


"Matt D Francis" <[email protected]> wrote in
message news:[email protected]...
> 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.

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" wrote:

> Hi Matt
>
> Create a Calculated field called Difference. Use the formula
> =AVERAGE(Diabetic)-AVERAGE('Non diabetic' )
>
> --
> Regards
>
> Roger Govier
>
>
> "Matt D Francis" <[email protected]> wrote in
> message news:[email protected]...
> > 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.
>
>
>

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.

--
Regards

Roger Govier


"Matt D Francis" <[email protected]> wrote in
message news:[email protected]...
> 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" wrote:
>
>> Hi Matt
>>
>> Create a Calculated field called Difference. Use the formula
>> =AVERAGE(Diabetic)-AVERAGE('Non diabetic' )
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Matt D Francis" <[email protected]> wrote in
>> message news:[email protected]...
>> > 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.
>>
>>
>>

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" wrote:

> 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.
>
> --
> Regards
>
> Roger Govier
>
>
> "Matt D Francis" <[email protected]> wrote in
> message news:[email protected]...
> > 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" wrote:
> >
> >> Hi Matt
> >>
> >> Create a Calculated field called Difference. Use the formula
> >> =AVERAGE(Diabetic)-AVERAGE('Non diabetic' )
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Matt D Francis" <[email protected]> wrote in
> >> message news:[email protected]...
> >> > 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.
> >>
> >>
> >>
>
>
>

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.

--
Regards

Roger Govier


"Matt D Francis" <[email protected]> wrote in
message news:[email protected]...
> 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" wrote:
>
>> 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.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Matt D Francis" <[email protected]> wrote in
>> message news:[email protected]...
>> > 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" wrote:
>> >
>> >> Hi Matt
>> >>
>> >> Create a Calculated field called Difference. Use the formula
>> >> =AVERAGE(Diabetic)-AVERAGE('Non diabetic' )
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> "Matt D Francis" <[email protected]> wrote in
>> >> message news:[email protected]...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>

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" wrote:

> 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.
>
> --
> Regards
>
> Roger Govier
>
>
> "Matt D Francis" <[email protected]> wrote in
> message news:[email protected]...
> > 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" wrote:
> >
> >> 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.
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Matt D Francis" <[email protected]> wrote in
> >> message news:[email protected]...
> >> > 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" wrote:
> >> >
> >> >> Hi Matt
> >> >>
> >> >> Create a Calculated field called Difference. Use the formula
> >> >> =AVERAGE(Diabetic)-AVERAGE('Non diabetic' )
> >> >>
> >> >> --
> >> >> Regards
> >> >>
> >> >> Roger Govier
> >> >>
> >> >>
> >> >> "Matt D Francis" <[email protected]> wrote in
> >> >> message news:[email protected]...
> >> >> > 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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

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.

--
Regards

Roger Govier


"Matt D Francis" <[email protected]> wrote in
message news:[email protected]...
> 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" wrote:
>
>> 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.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Matt D Francis" <[email protected]> wrote in
>> message news:[email protected]...
>> > 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" wrote:
>> >
>> >> 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.
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> "Matt D Francis" <[email protected]> wrote in
>> >> message news:[email protected]...
>> >> > 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" wrote:
>> >> >
>> >> >> Hi Matt
>> >> >>
>> >> >> Create a Calculated field called Difference. Use the formula
>> >> >> =AVERAGE(Diabetic)-AVERAGE('Non diabetic' )
>> >> >>
>> >> >> --
>> >> >> Regards
>> >> >>
>> >> >> Roger Govier
>> >> >>
>> >> >>
>> >> >> "Matt D Francis" <[email protected]> wrote
>> >> >> in
>> >> >> message
>> >> >> news:[email protected]...
>> >> >> > 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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

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

"Roger Govier" wrote:

> 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.
>
> --
> Regards
>
> Roger Govier
>
>
> "Matt D Francis" <[email protected]> wrote in
> message news:[email protected]...
> > 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" wrote:
> >
> >> 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.
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Matt D Francis" <[email protected]> wrote in
> >> message news:[email protected]...
> >> > 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" wrote:
> >> >
> >> >> 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.
> >> >>
> >> >> --
> >> >> Regards
> >> >>
> >> >> Roger Govier
> >> >>
> >> >>
> >> >> "Matt D Francis" <[email protected]> wrote in
> >> >> message news:[email protected]...
> >> >> > 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" wrote:
> >> >> >
> >> >> >> Hi Matt
> >> >> >>
> >> >> >> Create a Calculated field called Difference. Use the formula
> >> >> >> =AVERAGE(Diabetic)-AVERAGE('Non diabetic' )
> >> >> >>
> >> >> >> --
> >> >> >> Regards
> >> >> >>
> >> >> >> Roger Govier
> >> >> >>
> >> >> >>
> >> >> >> "Matt D Francis" <[email protected]> wrote
> >> >> >> in
> >> >> >> message
> >> >> >> news:[email protected]...
> >> >> >> > 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.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>