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.
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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
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.