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).
Bookmarks