Forum Discussion

Charmaine's avatar
Charmaine
Frequent Visitor
8 years ago
Solved

Sumif between two tables

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

Need Help
  • nickchobotar's avatar
    nickchobotar
    8 years ago

    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 -

6 Replies

  • nickchobotar's avatar
    nickchobotar
    Skilled Sharer
    8 years ago

    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 -

    • Charmaine's avatar
      Charmaine
      Frequent Visitor
      8 years ago

      Thank you so much, Nick. :smileywink::smileywink:

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 NumberProductQty
      7437402A1300
      7437402B700
      7437402D650
      7438014C800
      7438014D400
      7438093A1900
      7438842D750
      7438842C900

      Table 2

      Shipment NumberTotal Qty
      7437402 
      7438014 
      7438093 
      7438842 
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi Charmaine,

     

    Try this calculated column formula in Table2

     

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

     

    Hope this helps.