Pivot table calculated item problems

[Jim~C]

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]

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]

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

 

[Ken Wright]

You can turn off GETPIVOTDATA and fill down normally

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

 

[Zel Dolinsky]

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]

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