Problem using calculated item for grouping

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

green biro - 01 Feb 2008 23:57 GMT

I have a pivot table showing counts of grades achieved by students.
I can group some of these grades by adding a calculated item that just adds
together the number of, say, grade As and grade Bs. This seems more elegant
than the outline and grouping feature. So far so good.

However I have another table where I have the data field added twice: one
formatted as a percentage of the column. That works nicely too; I drag the
'data' field into the main body of the table and then label one column "Num"
and the other "per cent".

The problem is I can't now do my grouping (calculated item) as in the first
example because I get a message saying "Multiple data fields of the same
field not supported with calculated items". Would anyone be able to explain
to me why this restriction exists and if there is a workaround?

Thanks.

Reply to this Message

Herbert Seidenberg - 02 Feb 2008 16:33 GMT

Let's see your data at
http://www.freefilehosting.net/

Reply to this Message

Debra Dalgleish - 02 Feb 2008 19:41 GMT

I don't know why the restriction exists. As a workaround you could us a
calculation in the source data to do the grouping. Then add that field
to the pivot table.
Or add a column to the source data that is a link to the original data
cell, e.g. =C2
Then, use this field as the second copy in the pivot table's data area.

> I have a pivot table showing counts of grades achieved by students.
> I can group some of these grades by adding a calculated item that just adds
[quoted text clipped - 12 lines]
>
> Thanks.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

redrum - 02 Feb 2008 21:46 GMT

Thank you Debra.

I was planning to resort to adding a duplicate column but sounded out the
group because I wanted to make sure I hadn't missed anything.

It's strange when a restriction seeems to exist for no good reason. Ho hum,
at least I know now. Thanks again for your response.

GB

> I don't know why the restriction exists. As a workaround you could us a
> calculation in the source data to do the grouping. Then add that field
[quoted text clipped - 19 lines]
> >
> > Thanks.

Reply to this Message

Green Biro - 02 Feb 2008 22:11 GMT

Woops. I have just realised that adding a calculated item for my grouping
is flawed for my "percentage of column" figure because the extra item is
added to the column total!

I want to produce a pivot table that looks like this (where 'A', 'B' and 'C'
are passes).

Grade
A - 2 - 4%
B - 5 - 10%
C - 24 - 48%
Pass - 31 - 62%
D - 12 - 24%
E - 7 - 14 %
Fail - 19 - 38%
Total - 50 - 100%

How do I get this???

Thanks for any assistance.

GB

> Thank you Debra.
>
[quoted text clipped - 36 lines]
> > >
> > > Thanks.

Reply to this Message

Roger Govier - 02 Feb 2008 22:42 GMT

Hi

Assuming your data is in Columns A and B with Name in A and Grade in B.
Enter Group in C1
In C2 =IF(B2<="c","Pass","Fail")
And copy down.

Add column C range to your PT Range.
On the PT, make Group a Row item, followed by Grade
Drag Grade to the Data area as Count
Drag Grade to the Data area again>Options>Show data as % of column

On the Finished PT, drag Data and drop on Total, so you get the data as 2
columns.

Signature

Regards
Roger Govier

> Woops. I have just realised that adding a calculated item for my grouping
> is flawed for my "percentage of column" figure because the extra item is
[quoted text clipped - 65 lines]
>> > >
>> > > Thanks.

Reply to this Message

Green Biro - 03 Feb 2008 00:22 GMT

OK. Thanks. Guess I'll have to do it like that.

Didn't especially want to alter my source data as it's a little more
complicated than the example I gave but I can use the principle.

Sometimes I think PTs are amazing, other times I get disappointed by the
lack of functionality available.

Thanks again.

GB

> Hi
>
[quoted text clipped - 79 lines]
> >> > >
> >> > > Thanks.

Reply to this Message