Forum Discussion

AOD's avatar
AOD
Helper III
4 years ago
Solved

Conditions in calculate function

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 
ABC1000
DEF2000
XYZ3000

 

Table 2   
Order Product Total 
100001ABC10
100002DEF11
100003XYZ12
100004ABC13
100005DEF14
100006XYZ15

 

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 
100001ABC100.1(10/1000)*10
100002DEF110.055(11/2000)*10
100003XYZ120.04(12/3000)*10
100004ABC130.13(13/1000)*10
100005DEF140.07(14/2000)*10
100006XYZ150.05(15/3000)*10

 

tried using if conditions in calculate but it doesnt work,

Please help. 

Thanks.

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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:

     

     

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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: