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

[Phoenix538]

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?

[Phoenix538]

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

[rohit_singh · Accepted solution]

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

[Phoenix538]

@rohit_singh 

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

😀

[rohit_singh]

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

[rohit_singh]

Hello @Phoenix538 ,

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

Kind regards,

Rohit