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?
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.
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