Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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])
)
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |