I have a question on data blending. I have two data sources (both excel files) which have some overlapping field. Fields include store location (state), products sold (product a, b, c), sales person, etc.,
One data source has all of the relevant revenue information by account, products sold, sales person, branch, state etc,. The second has information on customer visits and don't necessarily tie back to an account, but has the same branch state, sales person, etc
I am trying to blend the data based on those overlapping fields. I would also like to be able to filter on those particular fields. I went through the blending tutorial and was able to create the sheet but am now having trouble with the filters.
If I just link both sheets on one category, for example branch location, both data sources will return the right information. When I want to drill in and filter on a second category, I run into the problem of it not being linked. If I link the file on 2 categories, I run into the problem of the secondary data source not having all of the information because it gets filtered out from the first. Here is an example of what happens:
If a product in branch A has no sales, but has 10 customer visits when I filter on the particular branch and product, I do not get any result for the visits since the primary data source doesn't have any revenue information associated with that. If the California office has 100 visits 50 for product A and 50 for product B, and product A doesn't have any existing sales, when I link by branch and product, I will not see the 50 visits associated with product A.
My question is, is there a way to have two separate sheets that have overlapping fields where I can use filters to display information from the second sheet that isn't dependent on a record existing in the 1st?