Conditions in calculate function

[AOD]

Hi All, 

Need help in achiveing below 

I have 2 tables , table 1 have Product rate , and Table 2 has order with product and total amount ,

Table 1
Product	Product Rate
ABC	1000
DEF	2000
XYZ	3000

Table 2
Order	Product	Total
100001	ABC	10
100002	DEF	11
100003	XYZ	12
100004	ABC	13
100005	DEF	14
100006	XYZ	15

Would like to derive new column in table 2 as below highlighted, 

Table 2
Order	Product	Total	Rate ( which is derived as ( total /Product rate )*10	Calculation
100001	ABC	10	0.1	(10/1000)*10
100002	DEF	11	0.055	(11/2000)*10
100003	XYZ	12	0.04	(12/3000)*10
100004	ABC	13	0.13	(13/1000)*10
100005	DEF	14	0.07	(14/2000)*10
100006	XYZ	15	0.05	(15/3000)*10

tried using if conditions in calculate but it doesnt work,

Please help. 

Thanks.

[MFelix · Accepted solution]

Hi @AOD 

I'm assuming that the rate table has distinct values for products, if this is not the case there is the need to make some changes to the calculation below, but you have two options:

Power Query

Add a new custom column with the following code:

([Total]/

(let Product = [Product] in Table.SelectRows(Rates, each [Product] = Product)){0}[Product Rate]) * 10

The important part is the let part of the syntax that picks up the value from the other table.

Calculated column (needs to have a relationship between both tables)

RAte = DIVIDE(Orders[Total ], RELATED(Rates[Product Rate ])) * 10

You can also use a calculated measure for this something similar to: