Calculate Intercompany Sales

[George1973]

Hi All,

Please assisit with the following issue:
I have a following sample data showing sales per customer and seller. The problem is that in the customer list I have also "sellers" meaning that those companies have sales between each others too,
I need to create a measure calculating:

a) Sales for only those companies which are the sellers in the same time - Let's call that measure "Intercompany Sales"

Customer_ID	Seller_ID	Amount
1	2	887
2	2	681
3	3	455
4	3	52
5	5	713
6	6	627
7	2	554
8	1	369
9	1	601
10	3	786
11	3	637
12	3	862
13	3	987
14	3	179
15	3	262
16	3	581
17	3	413
18	3	661
19	3	434

Thanks in advance

[mahoneypat · Accepted solution]

I think this approach will work in your measure expression

Sales From Companies that are also Sellers =
CALCULATE (
    SUM ( Table[Amount] ),
    TREATAS (
        VALUES ( Table[Seller_ID] ),
        Table[Customer_ID]
    )
)

Regards,

Pat

[mahoneypat · Accepted solution]

I think this approach will work in your measure expression

Sales From Companies that are also Sellers =
CALCULATE (
    SUM ( Table[Amount] ),
    TREATAS (
        VALUES ( Table[Seller_ID] ),
        Table[Customer_ID]
    )
)

Regards,

Pat

[George1973 · Accepted solution]

@mahoneypat  Thanks a lot.

What a wonderful measure -   TREATAS ().. I did not hava a clue about it.
Now I know, thanks to your support.

Best regards,

George

[George1973]

One clarification:

It should be a universal measure independent on the numbers of the "sellers" and "customers"