Apply exchange rates in a column containing values with 2 currencies

[Danoob]

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

Division	Currency	Country	Values
1​	EUR	France	-104​
1​	EUR	France	-190​
1​	EUR	France	-2932​
2​	EUR	USA	-2498​
2​	EUR	USA	-2142​
2​	USD	USA	-4397​
2​	USD	USA	-2031​
2​	USD	USA	-2031​
2​	USD	USA	-2720​

Country	Currency	TargetCurrency	ExRate
France	EUR	USD	1.1​
France	EUR	EUR	1​
USA	USD	USD	1​
USA	USD	EUR	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

 

[jdellasala]

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:

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:

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.

 

[Danoob]

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

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

 

[jdellasala]

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
	A	B	C	D	E
1	Currency	Amount		Currency	Amount
2	EUR	100		EUR	100
3	EUR	200		EUR	200
4	USD	300		USD	300
5	USD	400		USD	400
6				Total	1,000
7
8	EUR Total:	300		EUR Total:	300
9	USD Total:	700		USD Total:	700
10	TOTAL:	1,000		TOTAL:	1,000
Sheet1

Cell Formulas
Range	Formula
E6	E6	=SUBTOTAL(109,[Amount])
B8:B9	B8	=SUMIFS($B$2:$B$5,$A$2:$A$5,A8)
B10,E10	B10	=SUM(B8:B9)
E8:E9	E8	=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.

 

[Danoob]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Contract nb	Contract currency	Country	Amounts		Amounts in EUR	Amounts in USD		Currency
2	1	EUR	France	11927		11927	12754,7338		EUR/USD	$ 1,07
3	2	EUR	France	6584		6584	7040,9296		USD/EUR	€ 0,94
4	3	EUR	USA	5401		5401	5775,8294
5	4	USD	USA	8791		8219,585	8791
6	5	USD	USA	3540		3309,9	3540		Sum without amounts consolidated in one common currency	83989	Not correct
7	6	USD	USA	3979		3720,365	3979		Sum with amount consolidated in one currency (USD)	82041,665	OK
8	7	USD	USA	13649		12761,815	13649		Sum with amount consolidated in one currency (EUR)	87738,682	OK
9	8	EUR	USA	5351		5351	5722,3594
10	9	EUR	France	15354		15354	16419,5676
11	10	EUR	France	9413		9413	10066,2622
Feuil1

Cell Formulas
Range	Formula
F2:F11	F2	=IF(B2:B11="EUR",D2:D11*1,D2:D11*J3)
G2:G11	G2	=IF(B2:B11="USD",D2:D11*1,D2:D11*J2)
J2:J3	J2	=I2.Prix
J6	J6	=SUM(D2:D12)
J7	J7	=SUM(F2#)
J8	J8	=SUM(G2#)
Dynamic array formulas.