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:

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

Super User

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.

😀

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

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors