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.
Hello,
I have 2 tables linked by a many to many relationship: Order Header and Order Lines. In my scenario, one Order Line can be related to different Order Headers.
I'm trying to sum Order Lines Amount by Order Number. The problem is that the SUM function do not sum correctly Order Lines that exists in different Orders. For exemple, when I browse data in Excel :
My calculated sum is correct at Order header level but wrong at total level: as you can see, my total should be the sum of all Order Lines ( 26 357€) but I have 17 796€. The Order Line No 248849 that exist in both Order Headers is summed only one time (8 561€).
The measure "Amount" is defined this way in Order Line Table: Amount = SUM('Order Line'[Total Sale Price])
Total Sale Price is a calculated column based on 2 others columns of the Order Line table: Total Sale Price = Quantity * Price
The link between the 2 tables is done with a classic bridge table containing keys of Order Header and Order Lines tables
My question is, how can I sum all Order Line value, even when they exists in many Order Header ?
As I'm not a native english speaker, I hope my message is clear
Thank you for your help !
Hi @Anonymous ,
If your tables are many to many, and you have created a bridge, remember that filters flow downhill, and you have to select your filters from the bridge table. (Consider the bridge at the top.) If you choose 1 of the base tables to filter, the other table will not be filtered.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
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 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |