Forum Discussion
Condition Statement
I've seen many examples of how to handle currency but our company only wants the exchange rate to apply if the source currency is RMB. Then it should be converted to USD. The rest of the transactions, even if they aren't USD should be treated as if they are USD. I've spent way more time on this than probably necessary but here is what I have:
Table Erp CurrExRate holds the exchange rate values for each calendar day. I have it linked to the Date table and to the Bookings table using the SourceCurrCode. I created a new column called SourceTarget that is a combination of the Source and Target Curr Code fields.
Table BookingsTerritory holds the daily sales with the source currency code and I added a column that states if the source currency code is RMB, then the currency type should be RMBUSD. Otherwise, it will be USDUSD. I did this only to try and create something common between the two tables.
I want to create a new column or measure that states,
New Exchange Rate = If('BookingsTerritory[CurrencyType] = "RMBUSD", 'ERP CurrExRate'[NewExchange], 1.000000.
Basically, I need the daily exchange rate for any RMBUSD transactions but want to use 1.00000 if it is any other type of currency transaction. We don't convert to USD in that company by default and for the purpose of this report, they need me to. They don't want to convert any other currency types, though.
I don't get expected results when I use the condition statement above. Only some of the values fill in and they aren't correct. Could someone help with any ideas of a way to accomplish this? Thank you in advance!
Hello @karabryan ,
Could you please try to use firstnonblank in your formula like:
LOOKUPVALUE( FIRSTNONBLANK(Currency[NewRate],1), Currency[NewSourceTarget], Booking[NewSourceTarget] Currency[Date], Booking[Date] )If this post helps, then consider Accept it as the solution to help other members find it faster.
Best regards
Dedmon Dai
Hi karabryan ,
In order to have a more permanent solution believe you need to do the following:
- Create a measure with the following code:
ExchangeRate = MIN(ExchangeRateTable[ExchangeRate])- Now create a new measure to make the calculation:
Valueconverted = SUMX(Table, Table[Amount] * [ExchangeRate])Be aware that you need to have the columns from your many-to-1 relationship from the oneside on the visualization.
You SUMX table need to be adjusted to your model.
this is just a generic solution needs to be adjusted to your model and needs.
18 Replies
What is the best way to do that? This is one page in a .pbix with 60 tables and many pages of visuals. Last night I tried to use look-up functions but need multiple nested functions and that didn't work for me as expected. I also tried to merge the two tables in power query but that also didn't work because the exchange rate table had companies listed in it and although there is a relationship between the two, it didn't match them up correctly. I'm removing the company info from the exchange rate table because it is irrelevant and seeing if that helps. I can try to make a mock up file in the mean time if there is a way to isolate just that portion of my project.
