+ Reply to Thread
Results 1 to 4 of 4

Calculating a % of another item

  1. #1
    Registered User
    Join Date
    06-25-2020
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    2

    Calculating a % of another item

    I'm looking for a solution to calculate the % of an item in another field in a pivot table.

    This is the pivot table using sample data before adding the % of to the value calculations.
    Screenshot 2020-06-24 at 15.50.07.png

    Available = an item in a field called "Data Type" which shows the available time for staff in a team each month.
    Booked = another item in the field called "Data Type" which shows scheduled time for staff in a team each month.

    This is then broken down using the field "Job Type" which includes three items (in-house, quoted, and confirmed)

    I'm looking to report on team capacity so I need to calculate the the percentage of the item "Available" that is booked by each item in "Job Type". This cannot just be the percentage of the total of Job Type because the booked time may be more or less than the available time.

    I have set the value field to % of Available and this is the result:

    Screenshot 2020-06-24 at 16.01.27.png

    The available column is 100% as expected as the other values will be a % of this. However, I'm just getting n/a in the values for Job Type where I would expect to see the % of available. I'm assuming the pivot table doesn't like calculating against an item in another filed rather than its own parent.

    Is there a way to resolve this or an alternative way to calculate?

    I'm keen to keep the calculation within the pivot table because my aim is to use pivot charts with slicers to create a dashboard and the key to that is getting this % of calculation to work, or another method that gives the same result.

    Thanks
    Attached Files Attached Files
    Last edited by SymC1; 06-25-2020 at 03:35 AM. Reason: Added sample file

  2. #2
    Registered User
    Join Date
    09-04-2016
    Location
    Ursynow
    MS-Off Ver
    2016
    Posts
    4

    Re: Calculating a % of another item

    Please see the yellow banner near the top of the page and attach sample file

  3. #3
    Registered User
    Join Date
    06-25-2020
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Calculating a % of another item

    I have submitted a sample file. Thanks.

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

    Re: Calculating a % of another item

    Hello SymC1 and Welcome to Excel Forum.
    Not sure that I understand, however I'll make an attempt to help.
    Two columns are added to the source data
    The first column, Value/7, is populated using: =I2/7 (replacing the calculated field on the pivot table)
    The second column, % of Available, is populated using: =J2/AGGREGATE(15,6,J$2:J$49/((C$2:C$49="Available")*(H$2:H$49=H2)),1)
    On Sheet1 the values area contains the percentages produced by the second column.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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: 2
    Last Post: 02-24-2020, 10:32 AM
  2. [SOLVED] Setting age of item-calculating submission date age
    By ElizGreen in forum Excel General
    Replies: 4
    Last Post: 10-31-2018, 08:07 AM
  3. Calculating percentage and cost of an item
    By Dave1639 in forum Excel General
    Replies: 5
    Last Post: 06-27-2016, 06:12 PM
  4. Getting Item Rate from worksheet and calculating total item rates
    By skhari in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2015, 04:17 PM
  5. [SOLVED] Calculating a price per item with markup for two fields
    By rlowe in forum Excel General
    Replies: 3
    Last Post: 03-31-2014, 12:36 AM
  6. [SOLVED] Calculating sales with respect to item and in a particular month
    By peaceracer in forum Excel General
    Replies: 12
    Last Post: 07-19-2012, 03:23 AM
  7. Replies: 11
    Last Post: 06-28-2010, 11:44 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