Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
One clarification:
It should be a universal measure independent on the numbers of the "sellers" and "customers"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |