Forum Discussion

abfdjuk's avatar
abfdjuk
Icon for Helper I rankHelper I
4 years ago
Solved

currency conversion with budgeted rates

Hi.   I have two tables of data. One is the currency, year and exchange rate "ExchangeRates" the other is "Invoices" with colums for year, net amount and currency code. I have tried so many differe...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago
    Converted amount =
SUMX (
    Invoices,
    Invoices[Net amount]
        * LOOKUPVALUE (
            ExchRate[Rate],
            ExchRate[Currency code], Invoices[Currency code],
            ExchRate[Year], Invoices[Year]
        )
)
lukiz84's avatar
lukiz84
Icon for Memorable Member rankMemorable Member
4 years ago

yes, because YearCode in your table is of type "String" (text in PowerQuery) -> change it to integer (number in powerquery)

abfdjuk's avatar
abfdjuk
Icon for Helper I rankHelper I
4 years ago

nks for your quick response. Now I get this error: A table of multiple values was supplied where a single value was expected. Any ideas what I need to change?

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    it looks like you have multiple entries for the same combination of year and currency code. 

    • abfdjuk's avatar
      abfdjuk
      Icon for Helper I rankHelper I
      4 years ago

      Thanks for your help.