Currency Converter

[tejas1]

Hi , 

I am new to power bi. I  need your help. I have a fact table which is holding transaction amount with company id. I have  company master which is having currency field and the company id. I have currency table which having the Currency and exchange rate. I have joined Fact with company master with company id and currency master joined with Company master with currency. now I want to calculate the report currency which is transaction amount with company * exchange rate . how to write formula. I tried to write the formula with IF . If company id = XX then transaction * currency (exchange) which is not working kindly guide me.  I trying to make my consolidated financial Which will be in one currency. 

Kindly help me on this issue. 

Thanks in advance. 

[JarroVGIT · Accepted solution]

I've recreated your situation with the following tables:

Table TransactionsTable CompanyMasterTable CurrencyRates

PowerBI automatically created relationships. If this is not yet the case in your report, then create relationships Transactions[CompanyID] and CompanyMaster[CompanyID], and a relationship between CompanyMaster[Currency] and CurrencyRates[Currency]

Now, there are a few ways to get to your result, but what I've chosen to do was add a calculated column to the Transactions table:

AmountUSD = Transactions[AmmountLocalCurrency] * RELATED(CurrencyRates[RateUSD])

What is happening here is that it will look for the related RateUSD for every transaction. This is done through the relationship with CompanyMaster, and then furthermore with its relationship with CurrencyRates. This results in the following table (from the data view):

Note the additional column AmountUSD

Now you can use your Transaction table to create reports in just one currency (USD, in this case).

If this answered your question, please mark this as the solution.

[tejas1]

Thank you. It is working fine