Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!




Thank you. It is working fine

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.