Pivot table calculated item problems

Hi,

Given a set of raw data shaped like this:

Cat Item Value
--------------------
1 1 45
1 2 37
1 3 40
2 1 38
2 2 34
2 3 31
3 1 36
3 2 39
3 3 40
.......

I created a pivot table that uses "Cat" as a row field and "Value" as a data
field. I then change the summary function for the "Value" field to use
"Average". Next, I add the "Value" field as a data field again and this time
change the summary function to "StDev". This all works ok.

I'd like to add a calculated item to my pivot table that allows me to take
the standard deviation of "Value" and divide that by the average of "Value"
to calculate the coefficient of variance.

When I try to add a calculated item to calculate the coefficient of variance
(i.e. STDEV(value) / AVG(value) , I get the following error from Excel:

"Multiple data fields of the same field are not supported when a pivottable
report has calculated items"

However, even if I eliminate this problem, I get another message from Excel
when trying to add a calculated item as follows:

"Averages, standard deviations, and variances are not supported when a
pivottable report has calculated items"

Can anyone suggest how I would go about adding this calculation (i.e.
STDEV(value) / AVG(value) to my pivot table?

Thanks in advance,
Jim

You could do the calculation in a column adjacent to the pivot table,
but you won't be able to create a calculated field to do it in the pivot
table.

Jim~C wrote:
> Hi,
>
> Given a set of raw data shaped like this:
>
> Cat Item Value
> --------------------
> 1 1 45
> 1 2 37
> 1 3 40
> 2 1 38
> 2 2 34
> 2 3 31
> 3 1 36
> 3 2 39
> 3 3 40
> ......
>
> I created a pivot table that uses "Cat" as a row field and "Value" as a data
> field. I then change the summary function for the "Value" field to use
> "Average". Next, I add the "Value" field as a data field again and this time
> change the summary function to "StDev". This all works ok.
>
> I'd like to add a calculated item to my pivot table that allows me to take
> the standard deviation of "Value" and divide that by the average of "Value"
> to calculate the coefficient of variance.
>
> When I try to add a calculated item to calculate the coefficient of variance
> (i.e. STDEV(value) / AVG(value) , I get the following error from Excel:
>
> "Multiple data fields of the same field are not supported when a pivottable
> report has calculated items"
>
> However, even if I eliminate this problem, I get another message from Excel
> when trying to add a calculated item as follows:
>
> "Averages, standard deviations, and variances are not supported when a
> pivottable report has calculated items"
>
> Can anyone suggest how I would go about adding this calculation (i.e.
> STDEV(value) / AVG(value) to my pivot table?
>
> Thanks in advance,
> Jim
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Deb,

Thanks for your reply.

Hmm...I'm trying to create a reusable "template" for data analysis of this
data from a database. So, adding an adjacent column seems dangerous for two
reasons:

- The calculations would use GETPIVOTDATA function which does not fill
down correctly
- What happens if the user rearranges the pivot table. Does this destroy
the adjacent column and it's data?

It certainly helps to know that I'm barking up the wrong tree with trying
the calculated item. I'm scratching my head for a better approach...

Cheers,
Jim


"Debra Dalgleish" <[email protected]> wrote in message
news:[email protected]...
> You could do the calculation in a column adjacent to the pivot table, but
> you won't be able to create a calculated field to do it in the pivot
> table.
>
> Jim~C wrote:
>> Hi,
>>
>> Given a set of raw data shaped like this:
>>
>> Cat Item Value
>> --------------------
>> 1 1 45
>> 1 2 37
>> 1 3 40
>> 2 1 38
>> 2 2 34
>> 2 3 31
>> 3 1 36
>> 3 2 39
>> 3 3 40
>> ......
>>
>> I created a pivot table that uses "Cat" as a row field and "Value" as a
>> data field. I then change the summary function for the "Value" field to
>> use "Average". Next, I add the "Value" field as a data field again and
>> this time change the summary function to "StDev". This all works ok.
>>
>> I'd like to add a calculated item to my pivot table that allows me to
>> take the standard deviation of "Value" and divide that by the average of
>> "Value" to calculate the coefficient of variance.
>>
>> When I try to add a calculated item to calculate the coefficient of
>> variance (i.e. STDEV(value) / AVG(value) , I get the following error from
>> Excel:
>>
>> "Multiple data fields of the same field are not supported when a
>> pivottable report has calculated items"
>>
>> However, even if I eliminate this problem, I get another message from
>> Excel when trying to add a calculated item as follows:
>>
>> "Averages, standard deviations, and variances are not supported when a
>> pivottable report has calculated items"
>>
>> Can anyone suggest how I would go about adding this calculation (i.e.
>> STDEV(value) / AVG(value) to my pivot table?
>>
>> Thanks in advance,
>> Jim
>>
>>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>

You can turn off GETPIVOTDATA and fill down normally

See Debra's site at:-
http://www.contextures.com/xlPivot06.html

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip>

It seems to me that you could create a separate column (called CV) in the
Excel Table that calculates the CV in a specific Cell using the formula you
noted. Then when you create the Pivot table use that column in the data
area for the Pivot table.






"Jim~C" <jdchanATnovusint.com> wrote in message
news:[email protected]...
> Deb,
>
> Thanks for your reply.
>
> Hmm...I'm trying to create a reusable "template" for data analysis of this
> data from a database. So, adding an adjacent column seems dangerous for
two
> reasons:
>
> - The calculations would use GETPIVOTDATA function which does not fill
> down correctly
> - What happens if the user rearranges the pivot table. Does this
destroy
> the adjacent column and it's data?
>
> It certainly helps to know that I'm barking up the wrong tree with trying
> the calculated item. I'm scratching my head for a better approach...
>
> Cheers,
> Jim
>
>
> "Debra Dalgleish" <[email protected]> wrote in message
> news:[email protected]...
> > You could do the calculation in a column adjacent to the pivot table,
but
> > you won't be able to create a calculated field to do it in the pivot
> > table.
> >
> > Jim~C wrote:
> >> Hi,
> >>
> >> Given a set of raw data shaped like this:
> >>
> >> Cat Item Value
> >> --------------------
> >> 1 1 45
> >> 1 2 37
> >> 1 3 40
> >> 2 1 38
> >> 2 2 34
> >> 2 3 31
> >> 3 1 36
> >> 3 2 39
> >> 3 3 40
> >> ......
> >>
> >> I created a pivot table that uses "Cat" as a row field and "Value" as a
> >> data field. I then change the summary function for the "Value" field to
> >> use "Average". Next, I add the "Value" field as a data field again and
> >> this time change the summary function to "StDev". This all works ok.
> >>
> >> I'd like to add a calculated item to my pivot table that allows me to
> >> take the standard deviation of "Value" and divide that by the average
of
> >> "Value" to calculate the coefficient of variance.
> >>
> >> When I try to add a calculated item to calculate the coefficient of
> >> variance (i.e. STDEV(value) / AVG(value) , I get the following error
from
> >> Excel:
> >>
> >> "Multiple data fields of the same field are not supported when a
> >> pivottable report has calculated items"
> >>
> >> However, even if I eliminate this problem, I get another message from
> >> Excel when trying to add a calculated item as follows:
> >>
> >> "Averages, standard deviations, and variances are not supported when a
> >> pivottable report has calculated items"
> >>
> >> Can anyone suggest how I would go about adding this calculation (i.e.
> >> STDEV(value) / AVG(value) to my pivot table?
> >>
> >> Thanks in advance,
> >> Jim
> >>
> >>
> >
> >
> > --
> > Debra Dalgleish
> > Excel FAQ, Tips & Book List
> > http://www.contextures.com/tiptech.html
> >
>
>

Yes, that would be great except that calculating the CV is only valid when
using the averages calculated by the pivot table.

"Zel Dolinsky" <[email protected]> wrote in message
news:[email protected]...
> It seems to me that you could create a separate column (called CV) in the
> Excel Table that calculates the CV in a specific Cell using the formula
> you
> noted. Then when you create the Pivot table use that column in the data
> area for the Pivot table.
>
>
>
>
>
>
> "Jim~C" <jdchanATnovusint.com> wrote in message
> news:[email protected]...
>> Deb,
>>
>> Thanks for your reply.
>>
>> Hmm...I'm trying to create a reusable "template" for data analysis of
>> this
>> data from a database. So, adding an adjacent column seems dangerous for
> two
>> reasons:
>>
>> - The calculations would use GETPIVOTDATA function which does not
>> fill
>> down correctly
>> - What happens if the user rearranges the pivot table. Does this
> destroy
>> the adjacent column and it's data?
>>
>> It certainly helps to know that I'm barking up the wrong tree with trying
>> the calculated item. I'm scratching my head for a better approach...
>>
>> Cheers,
>> Jim
>>
>>
>> "Debra Dalgleish" <[email protected]> wrote in message
>> news:[email protected]...
>> > You could do the calculation in a column adjacent to the pivot table,
> but
>> > you won't be able to create a calculated field to do it in the pivot
>> > table.
>> >
>> > Jim~C wrote:
>> >> Hi,
>> >>
>> >> Given a set of raw data shaped like this:
>> >>
>> >> Cat Item Value
>> >> --------------------
>> >> 1 1 45
>> >> 1 2 37
>> >> 1 3 40
>> >> 2 1 38
>> >> 2 2 34
>> >> 2 3 31
>> >> 3 1 36
>> >> 3 2 39
>> >> 3 3 40
>> >> ......
>> >>
>> >> I created a pivot table that uses "Cat" as a row field and "Value" as
>> >> a
>> >> data field. I then change the summary function for the "Value" field
>> >> to
>> >> use "Average". Next, I add the "Value" field as a data field again and
>> >> this time change the summary function to "StDev". This all works ok.
>> >>
>> >> I'd like to add a calculated item to my pivot table that allows me to
>> >> take the standard deviation of "Value" and divide that by the average
> of
>> >> "Value" to calculate the coefficient of variance.
>> >>
>> >> When I try to add a calculated item to calculate the coefficient of
>> >> variance (i.e. STDEV(value) / AVG(value) , I get the following error
> from
>> >> Excel:
>> >>
>> >> "Multiple data fields of the same field are not supported when a
>> >> pivottable report has calculated items"
>> >>
>> >> However, even if I eliminate this problem, I get another message from
>> >> Excel when trying to add a calculated item as follows:
>> >>
>> >> "Averages, standard deviations, and variances are not supported when a
>> >> pivottable report has calculated items"
>> >>
>> >> Can anyone suggest how I would go about adding this calculation (i.e.
>> >> STDEV(value) / AVG(value) to my pivot table?
>> >>
>> >> Thanks in advance,
>> >> Jim
>> >>
>> >>
>> >
>> >
>> > --
>> > Debra Dalgleish
>> > Excel FAQ, Tips & Book List
>> > http://www.contextures.com/tiptech.html
>> >
>>
>>
>
>