Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Solution Sage
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.