cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tejas1
Regular Visitor

Currency Converter

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. 

 

1 ACCEPTED SOLUTION
JarroVGIT
Resident Rockstar
Resident Rockstar

I've recreated your situation with the following tables:

Table TransactionsTable TransactionsTable  CompanyMasterTable CompanyMasterTable CurrencyRatesTable 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 AmountUSDNote 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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
JarroVGIT
Resident Rockstar
Resident Rockstar

I've recreated your situation with the following tables:

Table TransactionsTable TransactionsTable  CompanyMasterTable CompanyMasterTable CurrencyRatesTable 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 AmountUSDNote 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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Thank you. It is working fine

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!