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:

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

Solution Sage

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.

😀

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

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

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

Top Solution Authors
Top Kudoed Authors