+ Reply to Thread
Results 1 to 3 of 3

Adding additional field to pivot table (but not for each column)

  1. #1
    Registered User
    Join Date
    06-01-2017
    Location
    Ayrshire, Scotland, UK, Europe, Eurasian Plate, Earth, Sol System, Sagitarius-Carena Arm, Milk Way, Known Universe
    MS-Off Ver
    2010
    Posts
    2

    Adding additional field to pivot table (but not for each column)

    Good Morning,

    I am currently using a pivot table to aggregate some labour-hour figures for various work centres, based on an imported list of operations being performed at those work centres.

    Capture.PNG

    These figures are generated by summing the work-hours for each work centre, grouped by job number.

    For instance, job number 12345 might have:
    12345 - clad - 12 hours
    12345 - clad - 10 hours
    12345 - CNC - 10 hours
    12345 - Inspection - 10 hours

    Which would be grouped into 12345 - clad = 22, CNC=10, Insp=10, Total = 42

    However, what we want to do is add an extra column for the sales order value to this table.

    The SO Val is the same for all jobs listed under 12345 - so using the max/average/min aggregate on the "value" field gives you the right value.

    However, when I try to add this in, it always adds either an extra column per work centre, as can be seen here: note I have changed the "Total" value to a "count" field to obfuscate the actual cost of jobs

    Capture2.PNG

    What I want to do is display this value at the end of each line, beside the "grand total" column.




    Any ideas how I might go about adding that last column in (preferably without using PowerPivot)

    Many thanks in anticipation!

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Adding additional field to pivot table (but not for each column)

    Without Power Pivot, I think your only option is hiding columns.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    06-01-2017
    Location
    Ayrshire, Scotland, UK, Europe, Eurasian Plate, Earth, Sol System, Sagitarius-Carena Arm, Milk Way, Known Universe
    MS-Off Ver
    2010
    Posts
    2

    Re: Adding additional field to pivot table (but not for each column)

    Quote Originally Posted by xlnitwit View Post
    Without Power Pivot, I think your only option is hiding columns.
    Oh-ho! Hiding the extras and just using the total row may be enough! On the other hand, it seems pretty fragile; I might need to just bite the bullet and use Power-Pivot.

    Oh well - thanks anyway! I might be able to get away with just using hidden columns

    --EDIT--

    The hidden-column method will have to do - it seems to give the results I'm after - though it's certainly not ideal! Just a pity there's not an easy way of doing it outside of powerpivot!

    Thanks for the help
    Last edited by cprobertson1; 11-22-2017 at 03:37 AM. Reason: Updating status

+ 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. Replies: 7
    Last Post: 02-27-2017, 07:41 AM
  2. Pivot Table adding additional columns excel 2007
    By runner in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-28-2012, 12:24 PM
  3. Adding additional colums in the pivot table
    By Excel Dumbo in forum Excel General
    Replies: 7
    Last Post: 08-21-2012, 04:00 AM
  4. Replies: 3
    Last Post: 03-03-2012, 12:16 PM
  5. Adding new calculated/column field to pivot table
    By MARYAN in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2010, 05:34 AM
  6. Replies: 3
    Last Post: 02-23-2009, 10:07 AM
  7. Pivot Table - adding a new field
    By Pivot Table in forum Excel General
    Replies: 2
    Last Post: 06-09-2005, 03:05 PM

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