cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gwright15 Regular Visitor
Regular Visitor

Sum data with multi relationships

Afternoon/Morning All

 

I am looking to calculate the total value of orders raised by a customer.  However, the deal record, has up to 7 places the customer can be recorded (such as the client, architect, QS, designer etc"), and can be recorded multi times.  For example, the customer could be the client, architect and QS.  As such, i only want to sum the deal value once, and not 3 times.

 

Due to the 7 fields the customer can be recorded, we have 7 relationships between the deal table and customer table, therefore i'm having to us 'USERELATIONSHIP' formaul to specfie the active relationship.

 

My current method, which is awful, is to run 7 sums and add these together.  This is both processor intenstive, and also mean the data can be counted multiple times.

 

My thought is using sumx, but not use to to link the multi relationships together.

 

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
HotChilli New Contributor
New Contributor

Re: 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)

12 REPLIES 12
Community Support Team
Community Support Team

Re: Sum data with multi relationships

Hi @gwright15 ,

 

I create a sample based on your requirement. However, it is too simple so that I can’t reproduce your scenario. Can you please post some sample data and excepted output and  share the details about the relationship between two tables? Then we can help you as soon as possible.

1.png

 

Best Regards,

Xue Ding 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

gwright15 Regular Visitor
Regular Visitor

Re: Sum data with multi relationships

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:

Annotation 2019-07-19 174233.png

 

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

HotChilli New Contributor
New Contributor

Re: 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)

Highlighted
Iamnvt Member
Member

Re: Sum data with multi relationships

@gwright15 

you can try to unpivot the Company in Power Query before using DAX.

Check the "New Deal" table here:

https://1drv.ms/u/s!Aps8poidQa5zk6sJ1uJqUK_yQvfiSA

gwright15 Regular Visitor
Regular Visitor

Re: Sum data with multi relationships

@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.

 

gwright15 Regular Visitor
Regular Visitor

Re: Sum data with multi relationships

@Iamnvt @HotChilli 

 

Thinking about this further, i think this will work. 

 

If the relationships looked like this now:

Annotation 2019-07-20 063409.png

Can i now link the sum the value of deals, where a user linked organisation contributed to a deal?

Iamnvt Member
Member

Re: Sum data with multi relationships

@gwright15 yes, you can enable bi-directional relationship between Deal an Organization link table.

gwright15 Regular Visitor
Regular Visitor

Re: Sum data with multi relationships

@Iamnvt awesome - would this need to be on all these relatioships or just certain ones?

Iamnvt Member
Member

Re: Sum data with multi relationships

no, only that that relationship.

Bi-directional is very dangerous to be abusive if you don't fully understand how it works.

 

for this case, only that relationship is required to perform the task you are looking for.

you may refer to this article for better understanding how it works:

https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 288 members 2,925 guests
Please welcome our newest community members: