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
Super User

Hi @Phoenix538 ,

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

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

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

Super User

Hi @Phoenix538 ,

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

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

Frequent Visitor

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

😀

Super User

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! 🙂

Super User

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