Sumif between two tables

[Charmaine]

Hello all, do you have any ideas how to merge two table with sumif condition?  

I have two tables as shown below and I would like to have a new column in TABLE 2 which in excel the new column would be  sumif(TABLE1[Medium Batch Name], TABLE2[Medium Batch Name], TABLE1[record_1])

TABLE 1

TABLE 2

TABLE 2

[Ashish_Mathur]

Hi @Charmaine,

Try this calculated column formula in Table2

=CALCULATE(SUM(Table1[Number]),FILTER(Table1,Table1[Text]=EARLIER(Table2[Text])))

Hope this helps.

[nickchobotar · Accepted solution]

@Charmaine

Here is one of the approaches to solve this given that you do not have a relationship between the two tables.

=CALCULATE(
SUM(Table1[Record 1]),
FILTER(
	Table1, 
	Table1[Col One] = CALCULATE(VALUES(Table2[Col One]))
	)
)

If these tables are related in your model your calc column DAX will be simply  = CALCULATE(SUM(Table1[Record 1]))

Thanks, Nick -

[Anonymous]

Is there something that would cause = CALCULATE(SUM(Table1[Record 1])) to not function properly?

I have a smiliar situation where I have two tables.  One table lists shipment numbers (Table 1 [Shipment Number]) and quantities of product on the shipment (Table 1[Qty]).  The shipment numbers in this table are not unique, as there is a separate line item for each item for each item shipped in the shipment as below.  What I would like to do is populate Table 2[Total Qty].  Shipment Numbers are related in a one to many relationship between the two tables.  I entered the formula =CALCULATE(SUM(Table 1[Qty])) and every cell shows up as a blank.  If I remove the CALCULATE function and only use sum, it sums all of the values, but appears to be unable to break them down by shipment.  For example, if 34,000,000 units were shipped in all shipments, that's the number I get in all cells.

Both shipment numbers are text and the Qty column is whole number (and I tried decimal number as well) and that doesn't appear to have solved it.  Any thoughts on something I might be overlooking that's preventing this from calculating?

Edit: I should note that I entered the above formula as a new column while in Table 2 and not within edit query.

Thanks

Table 1

Shipment Number	Product	Qty
7437402	A	1300
7437402	B	700
7437402	D	650
7438014	C	800
7438014	D	400
7438093	A	1900
7438842	D	750
7438842	C	900

Table 2

Shipment Number	Total Qty
7437402
7438014
7438093
7438842

[Ashish_Mathur]

Hi,

It looks like you are writing a calculated column rather than a measure.  Create a relationship from the Shipment Number column of Table1 to the Shipment Number column of Table2 (you do not need to have the Qty column in Table2).  To your visual, drag the Shipment Number column from Table2 and write this measure

=SUM(Table 1[Qty])

Hope this helps.

[Charmaine]

Thank you so much, Nick. 

[nickchobotar]

Thank you @Charmaine

Hopefully, it was helpful.