Forum Discussion
Helper VDAX multiply two columns in separate tables
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!
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!
6 Replies
FarhanAhmedCommunity Champion
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 likeAmount = Qty * RELATED (Price [Price])
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!
- amitchandak
Super User
CarlsBerg999 , new column in table 1
maxx(filter(Table2, Table2[product] = Table1[product]), Table[Price]) * Table1[Qty]
or
related(Table[Price]) * Table1[Qty]
CarlsBerg999Hi,
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?
- 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?
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!
