Aggregating or Sum items by Row while using Lookup Value

[dli9]

I have two tables (Product Text Numbers and Overall Product Volume). In the "Overall Product Volume" table, I would like to do a vlookup to find the sum of the Numeric Item text (found in the "Product Text Numbers" table) by Product type. The formula below is saying "the SUM function only accepts a column reference as an argument.". I do not want to create a measure for this, but rather a new column. The group by function wouldn't work either cause the "Numeric Item Text" column in "Product Text Numbers" IS a new column in the Data View. Please help!! 

Shirt Text Numbers
Product Type    ItemText                 Numeric Shirt Text
Jeans                  TIGER, BEAST               2
Jeans                  TIGER                           1
Tshirt                   BEAST                            1
Tanks                 RABBIT                            1
Tanks                RABBIT, TIGER, TIGER       3
Shoes                 RABBIT, BEAST, TIGER     3
Jackets                                                        0
Jackets              BEAST, BEAST, BEAST, BEAST, TIGER       5

Overall Product Volume
Product Type       Numeric Shirt Text
Jeans                         3
Tshirt                         1
Tanks                         4
Shoes                         3
Jackets                         5

[bcdobbs]

1) Create a relationship between the Product Type Columns.

2) In the Overall product volume table create a new column with the following dax:

Volume =

    CALCULATE ( 

        SUM('Shirt Text Numbers'[Numeric Shirt Text])

)

[ebeery · Accepted solution]

@dli9 , really questioning your overall approach here.... Nonetheless, a calculated column in "Overall Product Volume" like this should return the result you're looking for.

Numeric Shirt Text = 
VAR _CurrentProduct = 'Overall Product Volume'[Product Type]
VAR _Result = 
CALCULATE(
    SUM('Product Text Numbers'[Numeric Shirt Text]),
    'Product Text Numbers'[Product Type] = _CurrentProduct
)
Return
_Result

PS - in the future please include your data structured in a table so that it can be easily copy/pasted into Power BI. There are instructions here on how to do that.