DAX multiply two columns in separate tables

[CarlsBerg999]

Hi,

I have two tables. Table one:

Product	Sold qty
Product 1	1
Product 2	2
Product 3	51

Table two:

Product	Price
Product 1	50€
Product 2	100€
Product 3	25€
Product 4	30€
Product 5	25€

The goal is: Sold qty * Price for each product. Outcome would be:

Product	Total sales
Product 1	50€
Product 2	200€
Product 3	1275€

I know i could just merge these two tables in Power Query and make a calculated column. However, i want to do this with a DAX measure. How do i do this? (what kind of a relationship and DAX measure is required?)

Thanks for your help in advance!

[amitchandak]

@CarlsBerg999 , new column in table 1

maxx(filter(Table2, Table2[product] = Table1[product]), Table[Price]) * Table1[Qty]

or

related(Table[Price]) * Table1[Qty]

[CarlsBerg999]

 Hi, The second one (related.....) works fine as a new column. What is the idea behind the the: maxx(filter(Table2, Table2[product] = Table1[product]), Table[Price]) * Table1[Qty] Is this for a measure and if not, is it possible to do the required calculation as a measure rather than a calculcated column?

[Sumanth_23 · Accepted solution]

hello @CarlsBerg999,you can calculate revenue using DAX as below screenshot shown

Sales_DAX de productos: PRODUCTX(Product_Qty, Product_Qty[Sold qty]* RELATED(Product_price[Price]))

Please mark the post as a solution and provide a 👍 if my comment helped resolve your issue. Thank you!

[CarlsBerg999]

Hi,

The second one (related.....) works fine as a new column. What is the idea behind the the: 

maxx(filter(Table2, Table2[product] = Table1[product]), Table[Price]) * Table1[Qty]

Is this for a measure and if not, is it possible to do the required calculation as a measure rather than a calculcated column? 

[Sumanth_23]

hi @CarlsBerg999 - You can acheive this by creating a cacluated column; you can setup the 2 tables as seen below. 

Additionaly you can use the "RELATED" function and build a calculated column to calcuate the Sales

Revenue = Product_Qty[Sold qty] * RELATED(Product_price[Price])

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[FarhanAhmed]

If you have "Many to 1" or "1 to 1" relationship between ProductPrice and ProductSold table then use "RELATED" in "MANY" side of table to get the value from single side.

Lets say if you have Price table contains single row for each product and Sales table contains quantity sold of each time of product then you should create column in sold table like 

Amount = Qty * RELATED (Price [Price])