Apply exchange rates in a column containing values with 2 currencies

Danoob

New Member
Joined
May 31, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a table where I am trying to display the sum of the values in one currency or the other just by selecting in a slicer.
My data table in power query is like the 1st table (Table1) and my exchange rate table (ExRate) is like the 2nd one

DivisionCurrencyCountryValues
1​
EURFrance
-104​
1​
EURFrance
-190​
1​
EURFrance
-2932​
2​
EURUSA
-2498​
2​
EURUSA
-2142​
2​
USDUSA
-4397​
2​
USDUSA
-2031​
2​
USDUSA
-2031​
2​
USDUSA
-2720​


CountryCurrencyTargetCurrencyExRate
FranceEURUSD
1.1​
FranceEUREUR
1​
USAUSDUSD
1​
USAUSDEUR
0.89​

I used the following DAX formula, it works for France but not for USA

Sum of Rebate Settlement value in Selected Currency =
VAR MyRate =
CALCULATE (
FIRSTNONBLANK (Ex_Rate[ExRate],1),
FILTER (
Ex_Rate,
Ex_Rate[TargetCurrency] = SELECTEDVALUE ('Table1'[Values])
&& Ex_Rate[Currency] = SELECTEDVALUE (Ex_Rate[Currency])
)
)
RETURN
SUM ('Table1'[Values]) * MyRate

Can anyone help solving this ?

Thanks
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
If you're using 365, there's no need for PQ. You can get EXCHANGE rates of currencies using the currency codes found here in a FROM/TO combination such as EUR/USD to get the exchange rate of the Euro to US Dollar. Simply enter the currency code combination in a cell such as EUR/USD and then select Data, and in the Data Types drop down select Stocks. An icon will appear to the left of EUR/USD and a bar will appear to the right of the cell like this:
1676456976938.png

Clicking on the bar will show a list of items associated with the Data Type. Selecting Price will put the Exchange Rate to the right of the Data Type:
1676457157800.png

I'm not clear on what you're trying to do though. One Euro in France is worth one Euro in all 37 countries that use it, so one EUR in France only has relevance compared to another currency.
 
Upvote 0
If you're using 365, there's no need for PQ. You can get EXCHANGE rates of currencies using the currency codes found here in a FROM/TO combination such as EUR/USD to get the exchange rate of the Euro to US Dollar. Simply enter the currency code combination in a cell such as EUR/USD and then select Data, and in the Data Types drop down select Stocks. An icon will appear to the left of EUR/USD and a bar will appear to the right of the cell like this:
View attachment 85426
Clicking on the bar will show a list of items associated with the Data Type. Selecting Price will put the Exchange Rate to the right of the Data Type:
View attachment 85427
I'm not clear on what you're trying to do though. One Euro in France is worth one Euro in all 37 countries that use it, so one EUR in France only has relevance compared to another currency.
Hello,
What I'm trying to do is avoid to have a sum with amounts in both currency. Because doing the sum of 100€ and 200$ is not correct.
Maybe this will help to illustrate
1676481124492.png

I want to consolidate the amounts in both currencies. If you need more information or if I'm not clear enough let me know.
And I'm using DAX cause I would like to test it after in Power BI
 
Upvote 0
Hello,
What I'm trying to do is avoid to have a sum with amounts in both currency. Because doing the sum of 100€ and 200$ is not correct.
Maybe this will help to illustrate
View attachment 85475
I want to consolidate the amounts in both currencies. If you need more information or if I'm not clear enough let me know.
And I'm using DAX cause I would like to test it after in Power BI
Like this?
Book3
ABCDE
1CurrencyAmountCurrencyAmount
2EUR100EUR100
3EUR200EUR200
4USD300USD300
5USD400USD400
6Total1,000
7
8EUR Total: 300EUR Total: 300
9USD Total: 700USD Total: 700
10TOTAL: 1,000TOTAL: 1,000
Sheet1
Cell Formulas
RangeFormula
E6E6=SUBTOTAL(109,[Amount])
B8:B9B8=SUMIFS($B$2:$B$5,$A$2:$A$5,A8)
B10,E10B10=SUM(B8:B9)
E8:E9E8=SUMIFS(Table1[Amount],Table1[Currency],D8)

NOTE: The format of cells A8:A9 and D8:D9 is @" Total: " allowing the cells to just contain "EUR" or "USD" making it easier to reference them.
Also, it's a lot easier if the data is formatted as an Excel Table as it is in the Blue table.
It is also a LOT easier if you post data using XL2BB or at least posting it as a table.
 
Upvote 0
Okay I tried to use XL2BB.
I added two columns to show what the values would look like in a common currency and sums.
To reput in circonstance the database I'm dealing with stops at the amounts column (column D).
I want to consolidate the amounts either in EUR or in USD like in columns F and G.
Then if put a slicer to display the amounts in EUR or in USD so for example on line 2 if I would select EUR I would have 11927 and if I select USD I would see 12754.

Thanks,

Classeur1
ABCDEFGHIJK
1Contract nbContract currencyCountryAmountsAmounts in EURAmounts in USDCurrency
21EURFrance119271192712754,7338EUR/USD$ 1,07
32EURFrance658465847040,9296USD/EUR€ 0,94
43EURUSA540154015775,8294
54USDUSA87918219,5858791
65USDUSA35403309,93540Sum without amounts consolidated in one common currency83989Not correct
76USDUSA39793720,3653979Sum with amount consolidated in one currency (USD)82041,665OK
87USDUSA1364912761,81513649Sum with amount consolidated in one currency (EUR)87738,682OK
98EURUSA535153515722,3594
109EURFrance153541535416419,5676
1110EURFrance9413941310066,2622
Feuil1
Cell Formulas
RangeFormula
F2:F11F2=IF(B2:B11="EUR",D2:D11*1,D2:D11*J3)
G2:G11G2=IF(B2:B11="USD",D2:D11*1,D2:D11*J2)
J2:J3J2=I2.Prix
J6J6=SUM(D2:D12)
J7J7=SUM(F2#)
J8J8=SUM(G2#)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,822
Members
452,672
Latest member
missbanana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top