Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Trying to Multiply the Value in Rows from Two different tables

I have two different tables. One is a table with Currency rates. The other is a table with my sales orders. I have a relationship built in both tables where the Currency_Month_Year matches on a many to 1 relationship from sales orders to currency. I'm trying to put in a calculation where it multiplies my Net amount with my Value (Value is my currency rate) to give me a value in USD. 

 

I've tried using quick measures but it wants me to take count of value and I don't want to do that. 

 

In essence, it should be (SalesTable[Net amount] x (FX rates[Value])

 

Therefore in row 1, 0; row 2 = 1266, etc. 

 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi Anonymous

     

    Have you tried a calculated column in your SalesTable like this?

     

    New Column = 'SalesTable'[Net Amount] * RELATED('FX Rates'[Value])

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous

     

    Have you tried a calculated column in your SalesTable like this?

     

    New Column = 'SalesTable'[Net Amount] * RELATED('FX Rates'[Value])
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    If you want to get the desired value in calculated column, you could try this formula:

    Column = 'SalesTable'[Net Amount] * RELATED('FX Rates'[Value])

     

    If you want to do calculation via measure, you could try:

    Measure = MAX('SalesTable'[Net amount])*MAX('FX rates'[Value])

     

    Best regards,
    Yuliana Gu