+ Reply to Thread
Results 1 to 6 of 6

Pivot Table Referencing Two Data Tables Returns Incorrect Data

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    75

    Question Pivot Table Referencing Two Data Tables Returns Incorrect Data

    I’m trying to compare conflicting sets of tax output data to determine calculation differences between programs. I have put the outputs from the first program into one table and the outputs from another in a second table:

    Table 1 columns: City Name 1, Amount 1
    Table 2 columns: City Name 2, Amount 2

    I am then trying to compare the two tables via a pivot table listing the tax jurisdiction and its calculated amount in one table to its calculated amount in another table. I am wanting to see something like this in the pivot:

    Row: Sum of Amount 1: Sum of Amount 2:
    City A $1,950.59 $1,948.72

    I would then create a Max-Min column to determine respective differences outside of the pivot:

    Row: Sum of Amount 1: Sum of Amount 2: Max-Min:
    City A $1,950.59 $1,948.72 =max(h2:i2)-min(h2:i2)

    However, I am having a problem with my “Sum of Amount 2” column. I created a pivot table that added both output tables to my data model, and attempted the following arrangement:

    Filters: N/A
    Columns: Values
    Rows: City Name 1
    Values: Sum of Amount 1, Sum of Amount 2

    Excel prompted me to add relationships b/w the tables, so I did, using the City Names 1&2 as the primary/foreign key (though I was still prompted to add relationships). My pivot table returned the “Sum of Amount 1” values correctly, but my “Sum of Amount 2” values were all identical, simply the aggregated sum of the “amount 2” table column. How do I change this “Sum of Amount 2” column in my pivot to instead show the table 2 amount that matches the respective city name in table 1? I've not found any online articles that seem to point me in the right direction.

    Note: Program #2 calculates more jurisdiction data than Program #1, so more cities appear in table 2 than table 1. I am not concerned with these extra cities in this situation.

    Any help would be greatly appreciated, as this information is necessary for work! Please see included example file for reference (sensitive data scrubbed).
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Pivot Table Referencing Two Data Tables Returns Incorrect Data

    I'm not sure how to reference multiple tables in one PivotTable.

    That being said, I see that you are using a column built from formulas (column J).

    Why not build another column using formulas?

    That is, after removing the "Amount 2" from the PivotTable, try this in I2:

    =SUMIF(Table2[City Name 2],G2,Table2[Amount 2])

    See attachment for clarification.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    75

    Re: Pivot Table Referencing Two Data Tables Returns Incorrect Data

    Thanks, 63falcondude! I hadn't thought of using SUMIF with 2 separate tables. I had been trying to follow the below-linked article but was running into trouble. Would you or another user have time to help me figure the solution out from this angle? I feel this could be very helpful to know in many other situations, as well.

    http://www.k2e.com/tech-update/tips/...-model-feature

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Referencing Two Data Tables Returns Incorrect Data

    You can use PowerQuery only, for two tables
    See green table
    Last edited by sandy666; 10-16-2017 at 05:02 PM.

  5. #5
    Registered User
    Join Date
    10-21-2016
    Location
    Kansas City, MO
    MS-Off Ver
    2010, 2013
    Posts
    75

    Re: Pivot Table Referencing Two Data Tables Returns Incorrect Data

    Sandy666, you're saying I can only use Power Query to do this or that I can only use it for situations with only two tables? I'm understanding you as the former. Thanks for your help. Doesn't the article I linked, however, imply this can be done through the standard version of Excel without add-ins?

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Referencing Two Data Tables Returns Incorrect Data

    To take the result you can use only PowerQuery without additional things like formulas and something more.
    I didn't say the PowerQuery is a medicine for everything and you can't do that in another way.
    And with PowerQuery you can use as many tables as you wish.

    PowerQuery and PowerPivot are free add-ins from MS.

    I gave you solution with PowerQuery, maybe someone else will give solution without these add-ins.

+ 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. Pivot table displaying incorrect Column data
    By Mile029 in forum Excel General
    Replies: 11
    Last Post: 09-11-2018, 02:03 PM
  2. Replies: 19
    Last Post: 04-04-2016, 02:58 PM
  3. [SOLVED] Using Data in Pivot Table for refernce point returns #NAME?
    By JasonNeedsHelp in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-24-2015, 12:54 PM
  4. Replies: 0
    Last Post: 10-01-2014, 05:55 PM
  5. [SOLVED] Incorrect data in Pivot table
    By Reedberg in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 02-16-2013, 09:55 AM
  6. Replies: 1
    Last Post: 12-31-2010, 03:31 AM
  7. Referencing data from pivot tables
    By Dan27 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-14-2010, 01:36 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