+ 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
    17

    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
    17

    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
    UK
    MS-Off Ver
    2016 primarily
    Posts
    4,122

    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
    Sue, you're shouting at tea

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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