Probably Extremely Easy Question - Sum Column on one Table from Matching Rows on Other Table

[ytc-reports]

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.

[Vvelarde · Accepted solution]

@ytc-reports

Use this and let me know if works

QtySum = CALCULATE(Sum(TableB[Quantity]))

[ytc-reports]

@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!

[Anonymous]

Hi YTC have you looked at the Related() function?

[ytc-reports]

@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.