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  A...
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:

     

     

     

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: