Pivot Table calculated field

Kevali

New Member
I have a pivot table that shows all the teacher sets for a given year and a count of the feeder school.

Set School total
a anytown 2
a somewhere 3
Total 5
b anytown 2
b somewhere 3
Grand Total 10

What I need to be able to do is show the totals for all groups, a grand total and more diificult have the numbers shown as a percentage of the set not the Grand Total.

Kevin

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
So what results would you expect from the data above?

I would hope I can get the result to be

Set School total
a anytown 40%
a somewhere 60%
Total 50%
b anytown 40%
b somewhere 60%
Total 50%
Grand Total 100%

You may need two pivot tables to show all the results you want.
Book1
ABCDEFGHIJ
15Set as %age of Row
16SetSchoolTotalSum of TotalSchool
17aanytown1SetanytownsomewhereGrand Total
18asomewhere1a40.00%60.00%100.00%
19aanytown1b40.00%60.00%100.00%
20asomewhere1Grand Total40.00%60.00%100.00%
21asomewhere1
22banytown1
23bsomewhere1
24banytown1
25bsomewhere1Set as %age of Total
26bsomewhere1Sum of TotalSchool
27SetanytownsomewhereGrand Total
28a20.00%30.00%50.00%
29b20.00%30.00%50.00%
30Grand Total40.00%60.00%100.00%
Sheet1

To change to percentages select one of the data values, right click, field settings then Options, then change the "show data as" box

Hope it helps a bit

Replies
1
Views
595
Replies
0
Views
169
Replies
3
Views
149
Replies
0
Views
381
Replies
3
Views
903

1,219,770
Messages
6,150,167
Members
450,937
Latest member
kattyg261

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back