Forum Discussion

mersoz's avatar
mersoz
Copper Contributor
May 12, 2019

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?

BI & Data Analysis
excel
power bi

2 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    May 12, 2019
    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.
    • mersoz's avatar
      mersoz
      Copper Contributor
      May 12, 2019

      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 DAXTwifoo 

Resources