Forum Discussion
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)
Sorry for not coming back on this sooner.
I've attached an sample file, but will explain it alittle more.
So the data would look something like this:
Company 1 to 6 in the "Deal table" would be linked to the Organisation ID.
The query would be what are the value of deals (deals[value]) for each user, where their organisation is linked to a deal. Noting, on a deal, company 1 to 6, would be the same in theory, and therefore, only want to count the deal once.
So in the attached example, user 2003, is linked to organisation 1004, 1008, 1009, 1010, which in turn are linked to deals 1, 2, 3, 4, 6, 8, 9, 10
File is location here - https://www.dropbox.com/s/8u7spo9a767xh5g/Test%20file.pbix?dl=0
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)
you can try to unpivot the Company in Power Query before using DAX.
Check the "New Deal" table here:
HotChilli Sorry I didn’t explain this very well.
Company 1 to company 6 are organisations involved in a deal. So for example, an architect, a designer, a contractor, the client etc. The heading on the columns should read the above names. These are then linked to the organisation table. In theory we could have an organisation who are both the designer and architect.
So if you think of it with a house builder, you would have the house builder link, the brick layer link, the designer, the architect, the QS all involved in a single deal. All these can and do contribute to us winning a deal.
