Sum data with multi relationships

Whenever you see column names with xxx1, xxx2, xxx3 etc. the spider sense should start tingling.  Having a set of non-active relationships to deal with these columns also sets alarms off.

The Company columns should be unpivoted in Power Query to provide a column holding the Company name (company1, company2...) and a column holding the Company ID (1001, 1002...)

A single relationship can then be created between ID in Organisation and Company ID in the revamped Deal table.

The challenge that remains is to prevent double-counting of the Value field when linking to a User.

One way to do this would be : create a measure for the average(Value) and then use that in another measure like

Norm Value = SUMX(DISTINCT(Deal[ID]), [Avg Value])

This works out the average deal value based on a unique identifier in the Deal table (so prevents double counting)