Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 different formulas - all the ones I would use in excel don't seem to work! and keep hitting errors. The latest one I get is: a single value cannot be determined...
My question is how do I create a calculation which does does a currency conversion using the exchange rate in a set year from the "exchangeRates" table and then calculates this against the invoice amount?
many thanks for any help
Solved! Go to Solution.
Converted amount =
SUMX (
Invoices,
Invoices[Net amount]
* LOOKUPVALUE (
ExchRate[Rate],
ExchRate[Currency code], Invoices[Currency code],
ExchRate[Year], Invoices[Year]
)
)
Converted amount =
SUMX (
Invoices,
Invoices[Net amount]
* LOOKUPVALUE (
ExchRate[Rate],
ExchRate[Currency code], Invoices[Currency code],
ExchRate[Year], Invoices[Year]
)
)
I just tried this and get the following error: Function 'LOOKUPVALUE' does not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.
yes, because YearCode in your table is of type "String" (text in PowerQuery) -> change it to integer (number in powerquery)
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?
it looks like you have multiple entries for the same combination of year and currency code.
Thanks for your help.
ConvertedSum =
VAR InvoiceDate = SELECTEDVALUE(Invoices[InvoiceDate])
VAR Currency = SELECTEDVALUE(Invoices[Curreny])
VAR ConversionRate =
CALCULATE(
MIN(ConversionRates[rate]),
ConversionRates[currency] = Currency,
ConversionRates[cear] = YEAR(InvoiceDate)
)
RETURN SUM(Invoices[Invoices - Net amount]) * ConversionRate
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |