cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating a value to a different currency that is in a table

I have a table with contractual prices in the currency of the customer, this can be USD, SGD or USD.

I also have a table with itemdata, i.e. costprice, which is in USD.

In the matrix table I have the pricelists sorted by Currency Code, The costprice needs to be calculated to the currency of the pricelist.

I do have an exchange rate table in Power Bi with the exchange rate

So I need to do something like CostPrice FC = CostPrice USD * Rate of the Currency codee that is in the pricelist (Pricelist.Currency)

How do I do this?

1 ACCEPTED SOLUTION
Solution Sage

Hi @Phoenix538 ,

Thanks for providing the data. Based on that, I have a suggested solution using Power Query as shown below:

1) Load the tables

Table : FxRate

Table : Pricelist

2) Merge FxRate with Pricelist

On the pricelist table, go to the 'Home' ribbon and click on 'Merge Queries'

Create a join based on currency code

Expand the new column and select 'Rate'

You will see that a new column is added with the exchange rates. You can replace the null value with 1 since its USD to USD conversion.

3) Finally, add a column for Cost Price in USD, which is simply Price * FxRate

Please let me know if this suits your requirement. If not we can tweak the code to give you what you need.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

5 REPLIES 5
Frequent Visitor

Hello @rohit_singh ,

I have the ExchangeRates table

 CurrencyCode Description Date Rate AED Dihram - United Arab Emirates 01/05/2022 00:00:00 3,6731 CUS Euro (Customs NL) 01/05/2022 00:00:00 0,923359999999999 EUR Euro 01/05/2022 00:00:00 0,948399999999998 GBP Pound Sterling 01/05/2022 00:00:00 0,7965 NOK Norwegian Krone 01/05/2022 00:00:00 9,26740000000001 SGD Singapore Dollar 01/05/2022 00:00:00 1,3803

I will not copy the entire item and pricelist table.

But basically the Pricelist table looks like this

 Pricelist Article Description Currency Price A 1005.01 Pricelist A EUR 25,00 B 1005.01 Pricelist B EUR 26,00 C 1005.01 Pricelist C USD 18,00 D 1005.01 Pricelist D SGD 27,00

The matrix that I want to create looks like this:

 Currency Pricelist Costprice Sales Price EUR Pricelist A Costprice in USD calculated to Costprice in EUR Salesprice in EUR (which is already in correct currency) USD Pricelist C Costprice C SalesPrice in USD (which is already in correct currency)

I hope you know what I mean

Solution Sage

Hi @Phoenix538 ,

Thanks for providing the data. Based on that, I have a suggested solution using Power Query as shown below:

1) Load the tables

Table : FxRate

Table : Pricelist

2) Merge FxRate with Pricelist

On the pricelist table, go to the 'Home' ribbon and click on 'Merge Queries'

Create a join based on currency code

Expand the new column and select 'Rate'

You will see that a new column is added with the exchange rates. You can replace the null value with 1 since its USD to USD conversion.

3) Finally, add a column for Cost Price in USD, which is simply Price * FxRate

Please let me know if this suits your requirement. If not we can tweak the code to give you what you need.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

Frequent Visitor

You are a genius, I have it, never would have thought of this, this makes it a thousand times easier.

😀

Solution Sage

Haha thanks that's really kind of you @Phoenix538 . Happy that I was able to help !
Would really appreciate it if you could click on the thumbs up button and leave me kudos! 🙂

Solution Sage

Hello @Phoenix538 ,

Please provide sample data in text format (not a screenshot) and expected output for us to help you better.

Kind regards,

Rohit

Announcements

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

#### Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

#### The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors