+ Reply to Thread
Results 1 to 5 of 5

Pivot table - how can I express this data?

  1. #1
    Registered User
    Join Date
    08-24-2011
    Location
    West New York, NJ
    MS-Off Ver
    Excel 2003
    Posts
    4

    Pivot table - how can I express this data?

    Hi all,

    I have a question on pivot tables, or if there are any other methods - please feel free to suggest.

    I've attached my sheet on here, but essentially - I have a dataset that shows how much time a team will spend each month doing a task. The pivot table allows me to sum up the time per month by product - which gives an overview of how much time they'll need.
    But I need to overlay this data by the maximum time that each team has. so for example, if team apple has maximum time of 10, and sum of tasks is 5, ideally I'd like to show 50%, and if it's 15, then 150%.

    I tried playing around with "data bars" under conditional formatting, but this only shows a bar and doesn't provide a percentage. The goal is to show through sum of tasks if a team can do all of them, or they are over/under budgeted.

    I can get the total expected time to be spent by each team, and I know what the max the team can spend, but I don't know how to bring those two datasets together so that we can show what percentage of time they have is booked, under booked, or over booked.

    I know it's not the best explained post, so please let me know if you have any questions, and I will clarify. Thanks again for your help!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,008

    Re: Pivot table - how can I express this data?

    Lets at least initiate an exchange of ideas.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-24-2011
    Location
    West New York, NJ
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Pivot table - how can I express this data?

    Hi,

    Thank you. I've attached a new copy - ideally I want my data represented similarly to "final" sheet. I really appreciate your help on this - I'm not the best at working with excel in representing data. I want to get to a point where I can update the data sheet, and the "organized" data will reflect it.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,427

    Re: Pivot table - how can I express this data?

    I am doubtful that this is what you want, however perhaps it will get us moving in the right direction.
    Add the following Calculated Field to the pivot table: =Time/'Total Time'
    If my pivot table table isn't what you want then I suggest manually mocking up a what you would like the pivot table to look like (don't worry about the chart at the moment).
    Also please update your profile. The .xlsx is not supported by the 2003 version so I assume that you are using the 2007 or more recent version which may be important to know in order to help in the way that is most efficient.
    Let us know if you have any questions.
    Last edited by JeteMc; 12-16-2021 at 12:03 AM.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,008

    Re: Pivot table - how can I express this data?

    Playing with visualization again.
    The extra line with percentage I'm kind of close, I know were to look, but don't know how to do it.

+ 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: 21
    Last Post: 05-10-2018, 09:25 AM
  2. Replies: 6
    Last Post: 01-24-2017, 06:56 PM
  3. Automating Pivot table and pivot chart creation if data table names unknown
    By Vegiepie2016 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-04-2016, 11:52 AM
  4. [SOLVED] Using Calculated Field to Express % Change in Pivot Table Columns
    By eadamquinn in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-17-2015, 11:32 PM
  5. Replies: 1
    Last Post: 06-22-2010, 09:10 AM
  6. Replies: 1
    Last Post: 06-20-2010, 04:00 AM
  7. [SOLVED] Does Pivot Table express numbers in "thousands (000's)"?
    By Excelable in forum Excel General
    Replies: 2
    Last Post: 04-15-2006, 08:10 PM

Tags for this Thread

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