How to set relation between sales and territory/BU when sales can be counted to two territory/BU?

[tbones]

At first, thank you for your help, it is really appreciated!

I have the following situation with two imported tables:

Sales table:

Units	Product	Territory Nr	Business Unit
10	A	12345	GroupA
15	B	23456	GroupB
12	C	23456	GroupA/GroupB
10	D	34567	GroupA

The territory table, which I imported looks like this:

Territory Nr	Business Unit
12345	GroupA
12345	GroupB
23456	GroupA
23456	GroupB
34567	GroupA
34567	GroupB

GroupA/GroupB in the sales table means that sold units of certain products should count for both business units.

I would like to create a report using a matrix as follows:

Business Unit    Territory	Product A	Product B	Product C	Product D
GroupA    12345    23456    34567	10 - -	- - 15	- 12 -	- - 10
GroupB    23456	-	-	12	-

Could you please tell me how to adjust the tables and how to set a working relation between them?

Thank you again 🙂

[Pragati11 · Accepted solution]

Hi @tbones ,

You can create a relationship between these 2 tables using Territory column if in one of the tables this column has unique values.

If the Territory column in both tables have non-unique values, create a bridge table for this column and connect this bridge table to both of these tables. Follow following tutorial on bridge tables:

https://www.youtube.com/watch?v=vAvQ8pCnWDk

Thanks,

Pragati

[tbones]

the hint using a bridge table worked. Many thanks and have a great weekend!