Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a question today that I think most of you will find quite easy, but I'm really struggling with.
I have an Item table, and each row on the Item table of course represents one Item. Each item is identified by the No_ field.
No_ | Description |
A | Stock Item A |
B | Sales Item B |
C | Christmas Item C |
I have another table, purchase_lines, connected to it with a related field Item No_:
Item No_ | Quantity |
A | 2 |
A | 3 |
B | 6 |
C | 7 |
C | 3 |
What I need is a column on the first table that sums up matching rows on the second table.
Now your first instinct is going to be to tell me I can just drag the Quantity column into my report and it will sum up correctly there, and *you'd be correct*, but that's not what I need to do with this column. I'm doing some other complex stuff with other related tables and I really do need a calculated column on the Item table. A column that ends up looking like this:
No_ | Description | QtySum |
A | Stock Item A | 5 |
B | Sales Item B | 6 |
C | Christmas Item C | 10 |
I've tried Calculating and SumX-ing and I just can't figure out what to do. Please help.
Solved! Go to Solution.
Use this and let me know if works
QtySum = CALCULATE(Sum(TableB[Quantity]))
Use this and let me know if works
QtySum = CALCULATE(Sum(TableB[Quantity]))
@Vvelarde I think that may have worked! I feel so stupid, as that's so simple and I've used those things countless times before. I was just summing it, or trying to do calculate(sum and then putting a filter in there. I was overcomplicating it. Thanks so much!
Hi YTC have you looked at the Related() function?
@Anonymous yes and that works great in a 1:many relationship for referencing values on the 1: side of that relationship on the table with the :many side. But I'm doing the reverse, which is why I'm struggling.