Pivot tables - calculated fields and items

have a time series of volumes and revenues for a number of landfill sites.
Within a pivot table I have a calculated field working out average price. I
also have in the time dimension calculated items providing an indexation over
time of each field (volume, revenue and price against time zero).
Unfortunately the order of calculation seems predefined so that the
indexation is calculated first and the average price second which invalidates
my indexed average price. Has anyone come across this problem and is there
anything I can do to change this order or workaround this problem?

Hi Tim

From the PT toolbar, use the Pivot Table dropdown>Formulas>Solve order

--
Regards

Roger Govier


"Tim Wheeler" <Tim Wheeler@discussions.microsoft.com> wrote in message
news:F0E90CDF-A1A2-4877-9B2B-33B04BED2172@microsoft.com...
> have a time series of volumes and revenues for a number of landfill
> sites.
> Within a pivot table I have a calculated field working out average
> price. I
> also have in the time dimension calculated items providing an
> indexation over
> time of each field (volume, revenue and price against time zero).
> Unfortunately the order of calculation seems predefined so that the
> indexation is calculated first and the average price second which
> invalidates
> my indexed average price. Has anyone come across this problem and is
> there
> anything I can do to change this order or workaround this problem?
>

Hi Roger

Thanks for your response . Not quite what I was looking for - my fault as my
question is not particularly clear.

What I am trying to do is get my fields calculated before my items. I have
pasetd a simple example of what I am trying to do.

Absolute Index
Volume Revenue Price Volume Revenue Price
Year 1 35 700 20.00 0.0% 0.0% 0.0%
Year 2 33 696 21.09 -5.7% -0.6% 5.5%
Year 3 28 600 21.43 -20.0% -14.3% 7.1%
Year 4 24 601 25.04 -31.4% -14.1% 25.2%
Year 5 18 580 32.22 -48.6% -17.1% 61.1%

I have a time series of volumes and revenue and have defined a calculated
field to give me price. I have then defined items in the time dimension which
calculate indices of movements from Year 1. This works fine for the Volume
and Revenue items but does not work for Price because the pivot calculate the
items first and then the field second. So my Price index is calculated as
Revenue Index/Price Index which is wrong. It should be Revenue/Volume which
is then indexed. So for example the Year 5 Price Index should = 61.1% but the
PT will calculate this = 35.5%.

Sorry this is a bit long winded but hopefully you can see what I am trying
to do.

Any thoughts would be much appreciated.

Kind regards Tim

"Roger Govier" wrote:

> Hi Tim
>
> From the PT toolbar, use the Pivot Table dropdown>Formulas>Solve order
>
> --
> Regards
>
> Roger Govier
>
>
> "Tim Wheeler" <Tim Wheeler@discussions.microsoft.com> wrote in message
> news:F0E90CDF-A1A2-4877-9B2B-33B04BED2172@microsoft.com...
> > have a time series of volumes and revenues for a number of landfill
> > sites.
> > Within a pivot table I have a calculated field working out average
> > price. I
> > also have in the time dimension calculated items providing an
> > indexation over
> > time of each field (volume, revenue and price against time zero).
> > Unfortunately the order of calculation seems predefined so that the
> > indexation is calculated first and the average price second which
> > invalidates
> > my indexed average price. Has anyone come across this problem and is
> > there
> > anything I can do to change this order or workaround this problem?
> >
>
>
>

Hi Tim

Without knowing more about your underlying data table, it is difficult
to answer you correctly, but I was wondering why you made the Price a
calculated Item, rather than a calculated Field.
If you can, then remove the calculated item and insert instead a
calculated Field called Price, which is Revenue/Volume.
Then, repeating the 3 fields Volume, Revenue and price in the data area
as % difference from Year 1 data will give you the correct result.

--
Regards

Roger Govier


"Tim Wheeler" <TimWheeler@discussions.microsoft.com> wrote in message
news:CA2494B2-DEA2-41AD-8867-BCC6DB533307@microsoft.com...
> Hi Roger
>
> Thanks for your response . Not quite what I was looking for - my fault
> as my
> question is not particularly clear.
>
> What I am trying to do is get my fields calculated before my items. I
> have
> pasetd a simple example of what I am trying to do.
>
> Absolute Index
> Volume Revenue Price Volume Revenue Price
> Year 1 35 700 20.00 0.0% 0.0% 0.0%
> Year 2 33 696 21.09 -5.7% -0.6% 5.5%
> Year 3 28 600 21.43 -20.0% -14.3% 7.1%
> Year 4 24 601 25.04 -31.4% -14.1% 25.2%
> Year 5 18 580 32.22 -48.6% -17.1% 61.1%
>
> I have a time series of volumes and revenue and have defined a
> calculated
> field to give me price. I have then defined items in the time
> dimension which
> calculate indices of movements from Year 1. This works fine for the
> Volume
> and Revenue items but does not work for Price because the pivot
> calculate the
> items first and then the field second. So my Price index is calculated
> as
> Revenue Index/Price Index which is wrong. It should be Revenue/Volume
> which
> is then indexed. So for example the Year 5 Price Index should = 61.1%
> but the
> PT will calculate this = 35.5%.
>
> Sorry this is a bit long winded but hopefully you can see what I am
> trying
> to do.
>
> Any thoughts would be much appreciated.
>
> Kind regards Tim
>
> "Roger Govier" wrote:
>
>> Hi Tim
>>
>> From the PT toolbar, use the Pivot Table dropdown>Formulas>Solve
>> order
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Tim Wheeler" <Tim Wheeler@discussions.microsoft.com> wrote in
>> message
>> news:F0E90CDF-A1A2-4877-9B2B-33B04BED2172@microsoft.com...
>> > have a time series of volumes and revenues for a number of landfill
>> > sites.
>> > Within a pivot table I have a calculated field working out average
>> > price. I
>> > also have in the time dimension calculated items providing an
>> > indexation over
>> > time of each field (volume, revenue and price against time zero).
>> > Unfortunately the order of calculation seems predefined so that the
>> > indexation is calculated first and the average price second which
>> > invalidates
>> > my indexed average price. Has anyone come across this problem and
>> > is
>> > there
>> > anything I can do to change this order or workaround this problem?
>> >
>>
>>
>>

Hi Roger

Again I have not explained myself particularly clearly - apologies.

Price is indeed a calculated field in my PT. It is the fact that the items
calculate before the fields that is causing my problem.

Kind regards

Tim

"Roger Govier" wrote:

> Hi Tim
>
> Without knowing more about your underlying data table, it is difficult
> to answer you correctly, but I was wondering why you made the Price a
> calculated Item, rather than a calculated Field.
> If you can, then remove the calculated item and insert instead a
> calculated Field called Price, which is Revenue/Volume.
> Then, repeating the 3 fields Volume, Revenue and price in the data area
> as % difference from Year 1 data will give you the correct result.
>
> --
> Regards
>
> Roger Govier
>
>
> "Tim Wheeler" <TimWheeler@discussions.microsoft.com> wrote in message
> news:CA2494B2-DEA2-41AD-8867-BCC6DB533307@microsoft.com...
> > Hi Roger
> >
> > Thanks for your response . Not quite what I was looking for - my fault
> > as my
> > question is not particularly clear.
> >
> > What I am trying to do is get my fields calculated before my items. I
> > have
> > pasetd a simple example of what I am trying to do.
> >
> > Absolute Index
> > Volume Revenue Price Volume Revenue Price
> > Year 1 35 700 20.00 0.0% 0.0% 0.0%
> > Year 2 33 696 21.09 -5.7% -0.6% 5.5%
> > Year 3 28 600 21.43 -20.0% -14.3% 7.1%
> > Year 4 24 601 25.04 -31.4% -14.1% 25.2%
> > Year 5 18 580 32.22 -48.6% -17.1% 61.1%
> >
> > I have a time series of volumes and revenue and have defined a
> > calculated
> > field to give me price. I have then defined items in the time
> > dimension which
> > calculate indices of movements from Year 1. This works fine for the
> > Volume
> > and Revenue items but does not work for Price because the pivot
> > calculate the
> > items first and then the field second. So my Price index is calculated
> > as
> > Revenue Index/Price Index which is wrong. It should be Revenue/Volume
> > which
> > is then indexed. So for example the Year 5 Price Index should = 61.1%
> > but the
> > PT will calculate this = 35.5%.
> >
> > Sorry this is a bit long winded but hopefully you can see what I am
> > trying
> > to do.
> >
> > Any thoughts would be much appreciated.
> >
> > Kind regards Tim
> >
> > "Roger Govier" wrote:
> >
> >> Hi Tim
> >>
> >> From the PT toolbar, use the Pivot Table dropdown>Formulas>Solve
> >> order
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Tim Wheeler" <Tim Wheeler@discussions.microsoft.com> wrote in
> >> message
> >> news:F0E90CDF-A1A2-4877-9B2B-33B04BED2172@microsoft.com...
> >> > have a time series of volumes and revenues for a number of landfill
> >> > sites.
> >> > Within a pivot table I have a calculated field working out average
> >> > price. I
> >> > also have in the time dimension calculated items providing an
> >> > indexation over
> >> > time of each field (volume, revenue and price against time zero).
> >> > Unfortunately the order of calculation seems predefined so that the
> >> > indexation is calculated first and the average price second which
> >> > invalidates
> >> > my indexed average price. Has anyone come across this problem and
> >> > is
> >> > there
> >> > anything I can do to change this order or workaround this problem?
> >> >
> >>
> >>
> >>
>
>
>

Hi Tim

Sorry, I'm still not seeing the problem. Email me directly with a sample
sheet of your data and I will take a look.
Remove NOSPAM from my email address to send.

--
Regards

Roger Govier


"Tim Wheeler" <TimWheeler@discussions.microsoft.com> wrote in message
news:B04289A2-6A2D-4E68-BCD7-8B8BD59D44A4@microsoft.com...
> Hi Roger
>
> Again I have not explained myself particularly clearly - apologies.
>
> Price is indeed a calculated field in my PT. It is the fact that the
> items
> calculate before the fields that is causing my problem.
>
> Kind regards
>
> Tim
>
> "Roger Govier" wrote:
>
>> Hi Tim
>>
>> Without knowing more about your underlying data table, it is
>> difficult
>> to answer you correctly, but I was wondering why you made the Price a
>> calculated Item, rather than a calculated Field.
>> If you can, then remove the calculated item and insert instead a
>> calculated Field called Price, which is Revenue/Volume.
>> Then, repeating the 3 fields Volume, Revenue and price in the data
>> area
>> as % difference from Year 1 data will give you the correct result.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Tim Wheeler" <TimWheeler@discussions.microsoft.com> wrote in message
>> news:CA2494B2-DEA2-41AD-8867-BCC6DB533307@microsoft.com...
>> > Hi Roger
>> >
>> > Thanks for your response . Not quite what I was looking for - my
>> > fault
>> > as my
>> > question is not particularly clear.
>> >
>> > What I am trying to do is get my fields calculated before my items.
>> > I
>> > have
>> > pasetd a simple example of what I am trying to do.
>> >
>> > Absolute Index
>> > Volume Revenue Price Volume Revenue Price
>> > Year 1 35 700 20.00 0.0% 0.0% 0.0%
>> > Year 2 33 696 21.09 -5.7% -0.6% 5.5%
>> > Year 3 28 600 21.43 -20.0% -14.3% 7.1%
>> > Year 4 24 601 25.04 -31.4% -14.1% 25.2%
>> > Year 5 18 580 32.22 -48.6% -17.1% 61.1%
>> >
>> > I have a time series of volumes and revenue and have defined a
>> > calculated
>> > field to give me price. I have then defined items in the time
>> > dimension which
>> > calculate indices of movements from Year 1. This works fine for the
>> > Volume
>> > and Revenue items but does not work for Price because the pivot
>> > calculate the
>> > items first and then the field second. So my Price index is
>> > calculated
>> > as
>> > Revenue Index/Price Index which is wrong. It should be
>> > Revenue/Volume
>> > which
>> > is then indexed. So for example the Year 5 Price Index should =
>> > 61.1%
>> > but the
>> > PT will calculate this = 35.5%.
>> >
>> > Sorry this is a bit long winded but hopefully you can see what I am
>> > trying
>> > to do.
>> >
>> > Any thoughts would be much appreciated.
>> >
>> > Kind regards Tim
>> >
>> > "Roger Govier" wrote:
>> >
>> >> Hi Tim
>> >>
>> >> From the PT toolbar, use the Pivot Table dropdown>Formulas>Solve
>> >> order
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> "Tim Wheeler" <Tim Wheeler@discussions.microsoft.com> wrote in
>> >> message
>> >> news:F0E90CDF-A1A2-4877-9B2B-33B04BED2172@microsoft.com...
>> >> > have a time series of volumes and revenues for a number of
>> >> > landfill
>> >> > sites.
>> >> > Within a pivot table I have a calculated field working out
>> >> > average
>> >> > price. I
>> >> > also have in the time dimension calculated items providing an
>> >> > indexation over
>> >> > time of each field (volume, revenue and price against time
>> >> > zero).
>> >> > Unfortunately the order of calculation seems predefined so that
>> >> > the
>> >> > indexation is calculated first and the average price second
>> >> > which
>> >> > invalidates
>> >> > my indexed average price. Has anyone come across this problem
>> >> > and
>> >> > is
>> >> > there
>> >> > anything I can do to change this order or workaround this
>> >> > problem?
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>