+ Reply to Thread
Results 1 to 10 of 10

Pivot Table (Need :- % of Grand Total Column)

  1. #1
    Registered User
    Join Date
    07-03-2012
    Location
    md
    MS-Off Ver
    Excel 2010
    Posts
    37

    Pivot Table (Need :- % of Grand Total Column)

    I have a pivot table as shown in the attachment showing number of students enrolled by year and by month. when did they complete each of their assignments. I want to find the % completion of each assignment which is basically the total number of students enrolled to total number of each assignment complete. I can do it manually at the bottom of the row as shown in the attachment but i was hoping to make use of a adding a new field or some other way to automate it using pivot. Any help will be appreciated. Thanks
    Attached Files Attached Files

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Pivot Table (Need :- % of Grand Total Column)

    Hi sam99,

    You can do this with a Calculated field in the Pivot table.

    Go to the 'Options' Tab >> 'Formulas' >> 'Calculated Field'.

    You can create & add a New field that is a calculation of existing fields.

    Let me know if I can help further.

    Cheers
    Remember you are unique, like everyone else

  3. #3
    Registered User
    Join Date
    07-03-2012
    Location
    md
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Pivot Table (Need :- % of Grand Total Column)

    thanks but i could not do it. do u mind showing how to do it in the spreadsheet attached to my post
    Last edited by sam99; 12-11-2013 at 02:00 PM.

  4. #4
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Pivot Table (Need :- % of Grand Total Column)

    Hi sam99,

    Have a look at the posted file.

    Go to the 'Options' Tab >> 'Formulas' >> 'Calculated Field' & have a look at the Formulas.

    Cheers
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-03-2012
    Location
    md
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Pivot Table (Need :- % of Grand Total Column)

    Quote Originally Posted by noboffinme View Post
    Hi sam99,

    Have a look at the posted file.

    Go to the 'Options' Tab >> 'Formulas' >> 'Calculated Field' & have a look at the Formulas.
    Cheers
    Thanks mate - Exactly what I was looking for although I still do not have a clue on how the formula works;Would appreciate if you can throw in some light on this. Is there anyway the calculated field can be made a part of the original pivot table? It is extremely critical that I can include % column in the first table so it canbe published as one single report.

  6. #6
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Pivot Table (Need :- % of Grand Total Column)

    Hi sam99,

    This field is already a part of the Pivot table, add some other fields to the Pivot & see what happens.

    The calculation works as a new field created from the existing fields on the 'Student Data' worksheet, eg; the total numberof the 'Student Enrolment Dates' (438) from the 'Student Data' worksheet divided by the total of each ***.(x) ie ***.1 - ***.6.

    ***.1 total count is 438 also, so the division of the Enrolment Dates' (438) is 100%

    ***.2 total count is 225, so the division of the Enrolment Dates' (438) is 51%

    ***.3 etc etc

    The Formula you find in the 'Options' Tab >> 'Formulas' >> 'Calculated Field' >> '%_***_2' is as below;

    Please Login or Register  to view this content.
    Let me know how you go.

    I don't believe it, the censor has replaced the letters with '*'s.

    I guess you can guess the 3 letters that have been replaced - same as on your form.
    Last edited by noboffinme; 12-16-2013 at 02:51 AM.

  7. #7
    Registered User
    Join Date
    07-03-2012
    Location
    md
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Pivot Table (Need :- % of Grand Total Column)

    Sorry for the delayed response. I finally figured out what was going on. I tried to replicate what you had done but i get "div by zero" error for some reason. Not sure what mistake i am doing.
    You are right that the function is a part of the Pivot but it is a separate pivot. I intend to show it as a part of the original pivot so it is not confusing for people when they see it. but it appears that will not be possible. Correct me if i am wrong. I have uploaded my file for your review. Thanks again.
    Attached Files Attached Files

  8. #8
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Pivot Table (Need :- % of Grand Total Column)

    Hi sam99,

    OK, let's start again with a small dataset to illustrate the Calculated Field (CF) feature.

    The CF becomes a part of the Pivot, it isn't an extra Pivot.

    In the posted example, I got the sales amount & multiplied by 1.1 which over here is a Government Tax.

    You can see it doesn't exist in the original table beginning at Cell A1.

    Have a look at how this works.

    Re the DIV#0 error, that will happen if the Calculation has a number that is divided by a '0', you haven't done anything wrong.

    Let me know how you go.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-03-2012
    Location
    md
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Pivot Table (Need :- % of Grand Total Column)

    Thanks noboffinme for taking the time out to explain Calculated Fields.
    my first comment is about showing the "% complete" right below the "Grand Total" cells in the pivotable totals. I dont think I will be able to do it. If there is way to show this like Please can you manipulate the pivot table to show this?
    Second thing is about div#0; While I know I should get this error if I divide by 0, what stumps me is the fact that I used the same formula for calculated field as you did but I get div#0

  10. #10
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Pivot Table (Need :- % of Grand Total Column)

    Hi sam99,

    You won't have the % below the Grand Total if you use a CF, it will replace the Grand Total.

    I also think the 'Years' field you've added should be removed until you understand this feature better.

    My table & Pivot didn't produce the DIV#0 error because the data I used didn't include any zeros but yours did.

    Best to concentrate on getting the CF working & understood.

    Begin again with your own data, don't add the 'Years' field & try to get the same result as mine.

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot Table - Grand Total not the sum of the column
    By Harrold in forum Excel General
    Replies: 2
    Last Post: 10-25-2011, 11:23 AM
  2. pivot table and % on grand total column only
    By Mm73 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-02-2007, 09:41 AM
  3. Pivot Table Percentage of Grand Total Column
    By Jacob in forum Excel General
    Replies: 6
    Last Post: 03-08-2006, 01:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1