Problem using calculated item for grouping

[green biro]

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.

 

[Herbert Seidenberg]

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

 

[Debra Dalgleish]

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.

 

[redrum]

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

 

[Green Biro]

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

 

[Roger Govier]

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.

 

[Green Biro]

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