cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Phoenix538
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

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

rohit_singh_0-1652874919367.png

 

Table : Pricelist

rohit_singh_1-1652874951646.png

 

2) Merge FxRate with Pricelist

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

rohit_singh_2-1652875053754.png


Create a join based on currency code

rohit_singh_3-1652875088837.png

 

Expand the new column and select 'Rate'

rohit_singh_4-1652875158518.png

 

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.

rohit_singh_5-1652875220588.png

 

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

rohit_singh_6-1652875273367.png


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

View solution in original post

5 REPLIES 5
Phoenix538
Frequent Visitor

Hello @rohit_singh ,

I have the ExchangeRates table

 

CurrencyCodeDescriptionDateRate
AEDDihram - United Arab Emirates01/05/2022 00:00:003,6731
CUSEuro (Customs NL)01/05/2022 00:00:000,923359999999999
EUREuro01/05/2022 00:00:000,948399999999998
GBPPound Sterling01/05/2022 00:00:000,7965
NOKNorwegian Krone01/05/2022 00:00:009,26740000000001
SGDSingapore Dollar01/05/2022 00:00:001,3803

 

I will not copy the entire item and pricelist table. 

But basically the Pricelist table looks like this

 

PricelistArticleDescriptionCurrencyPrice
A1005.01Pricelist AEUR25,00
B1005.01Pricelist BEUR26,00
C1005.01Pricelist CUSD18,00
D1005.01Pricelist DSGD27,00

 

The matrix that I want to create looks like this:

 

CurrencyPricelistCostpriceSales Price
EURPricelist ACostprice in USD calculated to Costprice in EURSalesprice in EUR (which is already in correct currency)
USDPricelist CCostprice CSalesPrice in USD (which is already in correct currency)
    

 

I hope you know what I mean

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

rohit_singh_0-1652874919367.png

 

Table : Pricelist

rohit_singh_1-1652874951646.png

 

2) Merge FxRate with Pricelist

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

rohit_singh_2-1652875053754.png


Create a join based on currency code

rohit_singh_3-1652875088837.png

 

Expand the new column and select 'Rate'

rohit_singh_4-1652875158518.png

 

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.

rohit_singh_5-1652875220588.png

 

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

rohit_singh_6-1652875273367.png


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

@rohit_singh 

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

😀

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power Platform Conf 2022 768x460.jpg

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.

Top Solution Authors
Top Kudoed Authors