I have two data sets I'm trying to analyze, one has product specific sales data (VB Sales) and the other is leads and accounts pulled from Salesforce that also records if we've done any sales with the account (MasterProspectingData). The common field between the two Datasets is the City with 100K+ population that they fall within a 75 mile radius of.
I have a separate table that has the unique city list with a relationship field (city/city ID) tied to the city in both datasets.
I want to create a pivot table that lists all the cities in separate rows and the Firm Name from MasterProspectingData table as well as either FY_to_date_sales and or Prior_FY_Sales from the MasterProspectingData Table. However I also want it to be able to show the total product specific sales from the VBSales table by city. I can't currently get this to work as when I try to include the VBSales numbers it lists every single "firm name" from the MasterProspectingData next to the VB Sales total for that city even though those firms are not present within 75 miles of that city(per the MasterProspectingData table). Is there any way I can get it to show the VB sales total for that city (and ideally the VBSales "Customer Name") as well as the "Firm Names" that are present within 75 miles of those cities?
I'm attaching a screenshot of the relationship diagram, as well as the issue I run into. Note that if there are no VBSales for the City (Abilene) it displays the proper "firm name," but if there are VB Sales for the city (Akron) it incorrectly displays every single firm.
Capture.JPG
Capture2.JPG
Bookmarks