+ Reply to Thread
Results 1 to 3 of 3

Help with Pivot Table Formula based on subtotals

  1. #1
    Registered User
    Join Date
    01-09-2007
    Location
    Oregon
    Posts
    19

    Help with Pivot Table Formula based on subtotals

    I need to add a calculation to my pivot table that takes the subtotal of the dept hrs/employee total hrs and * 86.67 hrs. I am not sure how to do calculations in a pivot table based on subtotals and totals.

    For example:
    10-Employee Hrs Calc
    Dept 1
    2/21 4 (Tlt Dept1/Ttl 10-Employee)*86.67
    2/22 8 (Tlt Dept1/Ttl 10-Employee)*86.67
    Ttl Dept 1 12 (Tlt Dept1/Ttl 10-Employee)*86.67
    Dept 2
    2/21 4 (Tlt Dept2/Ttl 10-Employee)*86.67
    2/23 8 (Tlt Dept1/Ttl 10-Employee)*86.67
    Ttl Dept 2 12 (Tlt Dept1/Ttl 10-Employee)*86.67
    Ttl 10-Employee 24


    I have attached a sample Excel file.
    Last edited by lisa6538; 02-26-2020 at 09:14 PM.

  2. #2
    Registered User
    Join Date
    01-09-2007
    Location
    Oregon
    Posts
    19

    Re: Help with Pivot Table Formula based on subtotals

    Here's a screen shot of what I'm trying to accomplish. I want the calculation column embedded in the pivot table. Just not sure how to create the formula.

    Attachment 664762
    Last edited by lisa6538; 02-26-2020 at 09:19 PM.

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Help with Pivot Table Formula based on subtotals

    Do you have Power Pivot, and do you need the overall grand total to be the sum of the individual employee totals? If the answer is yes to both, then you can use three measures (assuming Table1 as your source):

    TotalHours: =SUM([Hours])
    AdjustedHours: =([TotalHours]/calculate([TotalHours],ALLEXCEPT(Table1,Table1[Employee Id])))*86.67
    FinalAdjHours: =if(ISFILTERED([Employee Id]),[AdjustedHours],SUMX(values(Table1[Employee Id]),[AdjustedHours]))

    as attached.
    Last edited by rorya; 02-27-2020 at 06:28 AM.
    Rory

+ 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. [SOLVED] Pivot Table AVG based on Subtotals
    By aetedford in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 05-16-2018, 06:22 AM
  2. Pivot table subtotals
    By laliparker in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 11-28-2013, 10:39 AM
  3. to add conditional Subtotals formula in a pivot table
    By Amit Lahoti in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-25-2013, 06:10 AM
  4. Get avg on subtotals in Pivot Table.
    By lokilewis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2012, 03:53 AM
  5. [SOLVED] Pivot Table Subtotals
    By Siyer in forum Excel General
    Replies: 3
    Last Post: 09-14-2005, 09:05 AM
  6. [SOLVED] pivot table row subtotals
    By david in forum Excel General
    Replies: 2
    Last Post: 06-29-2005, 07:05 PM
  7. [SOLVED] Pivot Table subtotals
    By Linda in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2005, 09:06 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