Pivot: Calculated Field vs Calculated Item

[SG]

Hello there,

I´ve been toying around a bit with Debra's approaches regarding Pivot
Tables, especially regarding multiple consolidation ranges and "how to
keep the same structure like in a normal PT" when your data is on
different sheets ..using MSQuery (Debra's example PT0007). Works like
a charm.

Now I am suffering on something else though and I do not seem to be
able to get it straight, hope you can help me.
Here is my structure:

Month Version
1
Brand Data Act LY ACTvsLY%
DUNBAR Sum of UNITS 736 1,135 -35%
Sum of SALES -83,883 -108,021 -22%
Sum of ANS (114) (95) 64%

I have the Fields "UNITS" & "SALES" ... and a calculated Field "ANS"
which is: SALES/UNITS

I have the field "Month" and "Version" , where the latter shows the
items "Act" (actual) and "LY" (last year)

Now I tried to calulate the %-change with a Calculated Item "ACTvsLY
%", which I did with formula: (Act/LY)-1.

My outcome is not completely satisfying... my -35% & -22% are correct
but my 64% is not.
Ain't it possible to change the order of calculation? So that I first
calculate my Field "ANS" and then my Item "ACTvsLY%"? So that I would
get: (114)/(95)-1 instead of -22%/-35%

Or do you have another solution...?

Thanks in advance!

 

[Roger Govier]

Hi

The way to do this would be to remove your calculated field called ANS,
and insert an extra column in your source table called ANS with a
formula which Divided your sales column cell by the Units cell.
Amend the data source for your PT to include this new column, Refresh
then add the newly created ANS field to your Pivot Table.

--
Regards

Roger Govier


Hello there,

I´ve been toying around a bit with Debra's approaches regarding Pivot
Tables, especially regarding multiple consolidation ranges and "how to
keep the same structure like in a normal PT" when your data is on
different sheets ..using MSQuery (Debra's example PT0007). Works like
a charm.

Now I am suffering on something else though and I do not seem to be
able to get it straight, hope you can help me.
Here is my structure:

Month Version
1
Brand Data Act LY ACTvsLY%
DUNBAR Sum of UNITS 736 1,135 -35%
Sum of SALES -83,883 -108,021 -22%
Sum of ANS (114) (95) 64%

I have the Fields "UNITS" & "SALES" ... and a calculated Field "ANS"
which is: SALES/UNITS

I have the field "Month" and "Version" , where the latter shows the
items "Act" (actual) and "LY" (last year)

Now I tried to calulate the %-change with a Calculated Item "ACTvsLY
%", which I did with formula: (Act/LY)-1.

My outcome is not completely satisfying... my -35% & -22% are correct
but my 64% is not.
Ain't it possible to change the order of calculation? So that I first
calculate my Field "ANS" and then my Item "ACTvsLY%"? So that I would
get: (114)/(95)-1 instead of -22%/-35%

Or do you have another solution...?

Thanks in advance!

 

[SG]

Hi Roger,

I have thought about that... and I have an ANS in my source table
already ... but that is on "sub"-Brand level! (ie. productlevel).
The PT aggregates my products and productgroups....

In my view I have to recalculate this ANS within the PT because the
sum of the "averages" at product level does not equal the "average" at
Brand level!

+++>
Month Version
1
Brand Data Act LY ACTvsLY%
DUNBAR Sum of UNITS 736 1,135 -35%
Sum of 0802 SALES -83,883 -108,021 -22%
Sum of ANSUP 1,420 1,299 9%

Any thought?