+ Reply to Thread
Results 1 to 4 of 4

Combining and subtracting results from two pivot tables (one dataset)

  1. #1
    Registered User
    Join Date
    02-17-2019
    Location
    Australia
    MS-Off Ver
    Office 2016 (Professional) Enterprise
    Posts
    2

    Combining and subtracting results from two pivot tables (one dataset)

    Hi there.

    I have a dataset which contains the following.

    Unique ID | File Owner | Start Date | Finish Date | Duration | Demanded $ | Received $ | Recovery Rate

    This data changes daily and contains many thousands of records.

    I have separate pivot tables recording the:
    - start dates (values) by owner (columns) by month (rows); and
    - finish dates (values) by owner (columns) by month (rows).

    I want to report data which shows the difference between the two - i.e. subtract the finished count in a month from those started in a month, by owner.

    Pulling both into a single pivot table doesn't work because the data all sorts based on the start date - the finish data does not use the "finish date" like the individual pivot table.

    Even if I could get this to work, using a calculated field subtracting "Count" of Start Date - "Count" of Finish Date also does not seem to work.

    Is there a way to report this data automatically? I'm told its possible using power query but not sure how to do this.

    A mocked up example of my tables can be found on link from Onedrive at 1drv.ms/x/s!AqLilZzOCuC8k3-U8ca2TI4c6Fau or as attached.
    Attached Files Attached Files
    Last edited by Thearo; 02-17-2019 at 06:53 AM. Reason: Edit - attached example

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Combining and subtracting results from two pivot tables (one dataset)

    Hi Thearo,
    I recommend you create your additional fields from your table on the 'Data' Worksheet and then Pivot the results.
    You say you want the difference of ... (I assume between the numeric fields from Columns 'E'-'H' for a month.
    Can you clarify exactly how you want to calculate this as many of your start & finish dates span more than one month.
    Do you want to count the number of months between Start & Finish & then divide the counts by that?
    Please give me some more info. Thanks
    Remember you are unique, like everyone else

  3. #3
    Registered User
    Join Date
    02-17-2019
    Location
    Australia
    MS-Off Ver
    Office 2016 (Professional) Enterprise
    Posts
    2

    Re: Combining and subtracting results from two pivot tables (one dataset)

    Hi there

    Thanks for your reply.
    I'm happy to add a column but I'm not sure what formula i'd use because it's aggregated data at pivot level that I in effect need to pivot again.

    What I'm looking to do is to Subtract (Count of Column C) from (Count of Column D) on a monthly basis, as reflected in the correct pivots in the two individual tables. This in effect gives a monthly balance of files (i.e. is the team/person finishing more files than they start? Or are they falling behind?)

    So using the Jan 2018 result, the team result would be 3, as there are 3 started and 0 finished files in that month overall. In Feb 2018, the result would be 1, as there are 3 opened files and 1 finished files in that month, etc etc. The same could be done for each individual person.

    This requires using the Count of finish date property which won't show in the combined in the pivot at the bottom - all data is pivoting on the start date. Thus, I in effect need to pivot the two tables at the top and get the result for each month.

    I've managed to do a hacky solution by making every month show and then simply subtracting the cells in excel the old fashioned way but there must be a better way as this breaks as soon as I filter these tables. I tried to do it with getpivotdata to preserve the references but then I can't fill down and its not feasible to hand code thousands of rows.

    I'm at a loss!

  4. #4
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Combining and subtracting results from two pivot tables (one dataset)

    Ok, can you be very specific about your calculations?

    Please Login or Register  to view this content.
    Which Worksheet do you mean - give an example of the result you expect.

+ 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. Combining pivot tables
    By mattmania in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-28-2017, 09:17 AM
  2. [SOLVED] Combining 2 pivot tables into 1 chart
    By B_Jarbs in forum Excel General
    Replies: 2
    Last Post: 04-05-2016, 09:39 AM
  3. Combining two tables into a pivot
    By c.drysdale89 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-04-2016, 07:33 AM
  4. Using Pivot Tables to Get Most Recent Piece of Data in a Large Dataset
    By kslattery in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-23-2015, 10:38 AM
  5. Combining Pivot Tables
    By BradP in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-15-2014, 10:10 AM
  6. Replies: 2
    Last Post: 08-16-2010, 06:40 PM
  7. [SOLVED] Combining 2 pivot tables
    By RestlessAde in forum Excel General
    Replies: 1
    Last Post: 08-04-2005, 09: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