Aggregating or Sum items by Row while using Lookup Value

Question

I have two tables (Product Text Numbers and Overall Product Volume). In&nbsp;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!!&nbsp;Shirt Text NumbersProduct Type&nbsp; &nbsp; ItemText&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Numeric Shirt TextJeans&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TIGER, BEAST&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2Jeans&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TIGER&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Tshirt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BEAST&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Tanks&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;RABBIT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Tanks&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RABBIT, TIGER, TIGER&nbsp; &nbsp; &nbsp; &nbsp;3Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;RABBIT, BEAST, TIGER&nbsp; &nbsp; &nbsp;3Jackets&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0Jackets&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; BEAST, BEAST, BEAST, BEAST, TIGER&nbsp; &nbsp; &nbsp; &nbsp;5Overall Product VolumeProduct Type&nbsp; &nbsp; &nbsp; &nbsp;Numeric Shirt TextJeans&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3Tshirt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1Tanks&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4Shoes&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3Jackets&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp;&nbsp;

anonymous · Accepted Answer

Anonymous&nbsp;, 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&nbsp;PS - in the future&nbsp;please include your data structured in a table so that it can be easily copy/pasted into Power BI. There are instructions&nbsp;here&nbsp;on how to do that.

Forum Discussion

Aggregating or Sum items by Row while using Lookup Value

2 Replies

Related content

All items from contracts where the contracts contain a certain item on them that I lookup.

Lookup help

Data Aggregation issue

Top Items (Comparison / Lookup)

Aggregate