Trouble Creating Relationships 2

[PaulCo]

I recently posted a question about creating releationships with multiple relationships. Trouble Creating Relationships 

Thanks to the people that helped, I was able to rearrange my data in a more efficient way.

However, I wasn't able to slice the data how I needed to due to duplicates in one of the tables.

Below I'll restate the problem with a new dataset to see if anyone is able to help.

I have 2 tables of data. One is a report from SFDC which shows upcoming bookings and the other is a table created in excel which shows the plan numbers for the next 2 quarters. I would like to create a relationship or merge these 2 tables so that I can slice the data by Opportunity Type, Region and Period so that I can compare the Plan number to the Bookings number.

For example if I have 2 'Cards', one showing total booking number and one showing total plan number, and I create a slicer for period and select 2017-Q1, then only the Q1 data totals will be shown for each. 

Here are my 2 tables:

Bookings Plan
Opportunity Record Type	Month	Region	Period	Plan Amount
X	10/1/2016	EMEA	Q1-2017	300
X	11/1/2016	EMEA	Q1-2017	400
X	12/1/2016	EMEA	Q1-2017	1000
X	1/1/2016	EMEA	Q2-2017	300
X	2/1/2016	EMEA	Q2-2017	200
X	3/1/2016	EMEA	Q2-2017	1200
Y	10/1/2016	EMEA	Q1-2017	100
Y	11/1/2016	EMEA	Q1-2017	100
Y	12/1/2016	EMEA	Q1-2017	800
Y	1/1/2016	EMEA	Q2-2017	300
Y	2/1/2016	EMEA	Q2-2017	400
Y	3/1/2016	EMEA	Q2-2017	1000
X	10/1/2016	North America	Q1-2017	500
X	11/1/2016	North America	Q1-2017	600
X	12/1/2016	North America	Q1-2017	1200
X	1/1/2016	North America	Q2-2017	500
X	2/1/2016	North America	Q2-2017	400
X	3/1/2016	North America	Q2-2017	1400
Y	10/1/2016	North America	Q1-2017	300
Y	11/1/2016	North America	Q1-2017	300
Y	12/1/2016	North America	Q1-2017	1000
Y	1/1/2016	North America	Q2-2017	500
Y	2/1/2016	North America	Q2-2017	600
Y	3/1/2016	North America	Q2-2017	1200

SFDC
Opportunity Name	Account Name	Opportunity Record Type	Month	Period	Region	Amount
Opportunity A	Company A	X	2/1/2016	Q2-2017	EMEA	400
Opportunity B	Company A	Y	3/1/2016	Q2-2017	EMEA	600
Opportunity C	Company C	X	10/1/2016	Q1-2017	EMEA	700
Opportunity D	Company C	X	11/1/2016	Q1-2017	EMEA	100
Opportunity E	Company C	Y	1/1/2016	Q2-2017	EMEA	1000
Opportunity F	Company C	Y	2/1/2016	Q2-2017	North America	200
Opportunity G	Company A	X	3/1/2016	Q2-2017	North America	300
Opportunity H	Company C	X	10/1/2016	Q1-2017	North America	800
Opportunity I	Company C	X	12/1/2016	Q1-2017	North America	900
Opportunity J	Company A	Y	1/1/2016	Q2-2017	North America	900
Opportunity K	Company B	Y	2/1/2016	Q2-2017	North America	300
Opportunity L	Company B	X	1/1/2016	Q2-2017	EMEA	200
Opportunity M	Company B	X	2/1/2016	Q2-2017	EMEA	100
Opportunity N	Company A	Y	3/1/2016	Q2-2017	North America	800
Opportunity O	Company B	X	10/1/2016	Q1-2017	EMEA	700

When I tried to use the previous solution I was getting repeats of the plan numbers as they were showing for each period on the SFDC numbers.

The plan numbers have one record for each Opportunity type for each period for each region. The SFDC table can have any number of records for each field.

All help is greatly appreciated.

Thanks

Paul 

[CahabaData · Accepted solution]

It appears to me that you need to create a stand alone reference table; the combination of [month & region] appears to be the viable value.

Create this combination column in both tables.

Then create a new reference table that is the Distinct list of the Combination.  It must contain all without duplicates.  If neither table contains all then you need to append them together into a single column before creating the Distinct.  Or it maybe easier just create the table manually rather than derive it from your existing tables - whichever is a more efficient method - and whether this table is to grow automatically over time as the data is refreshed.

Then the reference table is a single column table.  Join it to the two tables using the new combination fields. 

You should then be able to report & filter on these 2 tables together.

[PaulCo]

This worked perfectly

Thanks!