 Highlighted

# Calculated column multiply with lookup table value

I have a transaction table and this is linked to a different table for product lookup. In the product look up, I have the price of each product.

I want to make a new calculated column or measure where I can use the already available information in the transactions table and multiply one of the columns here with the price column in the product lookup table.

How can I do this? Since I can't use an aggregate function with the lookup table, what is the way to do this?

2 Replies
Highlighted

# Re: Calculated column multiply with lookup table value

If I imagine it right, you have a Quantity column in your Transactions table that you want multiplied by the Price in the Product table. I suggest you insert a Price column and a Total column in the Transactions table. Then, use a formula like this to the Price column:
=VLOOKUP(Product,ProductTable,
MATCH(Price,ProductLabels,0),0)
Finally, you simply multiply the values in the Quantity and Price columns to determine the values in the Total column.
Highlighted

# Re: Calculated column multiply with lookup table value

Sorry I should have clearifid my question. This is by using Power Pivot and DAX not Excel formulas. I can do this in Excel without a problem. The problem I am having is when I use data model and DAX@Twifoo