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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors