Home

Calculated column multiply with lookup table value

%3CLINGO-SUB%20id%3D%22lingo-sub-561261%22%20slang%3D%22en-US%22%3ECalculated%20column%20multiply%20with%20lookup%20table%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561261%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20transaction%20table%20and%20this%20is%20linked%20to%20a%20different%20table%20for%20product%20lookup.%20In%20the%20product%20look%20up%2C%20I%20have%20the%20price%20of%20each%20product.%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20to%20make%20a%20new%20calculated%20column%20or%20measure%20where%20I%20can%20use%20the%20already%20available%20information%20in%20the%20transactions%20table%20and%20multiply%20one%20of%20the%20columns%20here%20with%20the%20price%20column%20in%20the%20product%20lookup%20table.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20do%20this%3F%20Since%20I%20can't%20use%20an%20aggregate%20function%20with%20the%20lookup%20table%2C%20what%20is%20the%20way%20to%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-561261%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561287%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20column%20multiply%20with%20lookup%20table%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561287%22%20slang%3D%22en-US%22%3EIf%20I%20imagine%20it%20right%2C%20you%20have%20a%20Quantity%20column%20in%20your%20Transactions%20table%20that%20you%20want%20multiplied%20by%20the%20Price%20in%20the%20Product%20table.%20I%20suggest%20you%20insert%20a%20Price%20column%20and%20a%20Total%20column%20in%20the%20Transactions%20table.%20Then%2C%20use%20a%20formula%20like%20this%20to%20the%20Price%20column%3A%3CBR%20%2F%3E%3DVLOOKUP(Product%2CProductTable%2C%3CBR%20%2F%3EMATCH(Price%2CProductLabels%2C0)%2C0)%3CBR%20%2F%3EFinally%2C%20you%20simply%20multiply%20the%20values%20in%20the%20Quantity%20and%20Price%20columns%20to%20determine%20the%20values%20in%20the%20Total%20column.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561623%22%20slang%3D%22en-US%22%3ERe%3A%20Calculated%20column%20multiply%20with%20lookup%20table%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561623%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20I%20should%20have%20clearifid%20my%20question.%20This%20is%20by%20using%20Power%20Pivot%20and%20DAX%20not%20Excel%20formulas.%20I%20can%20do%20this%20in%20Excel%20without%20a%20problem.%20The%20problem%20I%20am%20having%20is%20when%20I%20use%20data%20model%20and%20DAX%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
mersoz
New Contributor

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies