How to create a measure to calculate the sum amount of sales with a Target Currency (EUR)

[sunyangjun1115]

Hi All,

need some help on  below requirment. 

i have a Sales Table in Power BI , it contains the amount of sales and the corrensponding Currency. see below screenshot.

Item	Curency	Amount
1	CNY	100
2	CNY	300
3	HKD	200
4	INR	1000
5	INR	2000
6	NOK	500

besides, i also have another table which contains the exchange rate with 2 years data.

Year	From Currency	To Currency	Rate	Description
2023	CNY	EUR	0.135	In Year 2023, 1 CNY = 0.135 EUR
2023	HKD	EUR	0.121
2023	INR	EUR	0.012
2023	NOK	EUR	0.099
2024	CNY	EUR	0.127
2024	HKD	EUR	0.116
2024	INR	EUR	0.011
2024	NOK	EUR	0.089

my requirment is to create a measure which can calcuate the total amount of the sales with currency EUR, based on the selection of the year( put a slicer in the report page , it will has 2 value, one is 2023, and another is 2024). how to realize this ? 

thanks in advance for your time and support. 

[ValtteriN]

Hi,

Here is a measure for this:

To eur =

var _year = MAX('Table (39)'[Year])

var _currency = MAX('Table (38)'[Curency])

var _rate = CALCULATE(MAX('Table (39)'[Rate]),_currency='Table (39)'[From Currency],'Table (39)'[To Currency]="EUR")

RETURN

_rate*SUM('Table (38)'[Amount])


End result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[sunyangjun1115]

HI ValtteriN,

many thanks for your help. i tried the measure that you created. seems the total amount is not correct. for example, if we choose 2024 as the year value , and total amount of sales is 364.90.  actually, the number should be 151.5(12.7+38.1+23.2+11+22+44.5)

[ValtteriN · Accepted solution]

Hi,

Yes, sorry about that I forgot to fix the total calculation. 

To eur =

var _year = MAX('Table (39)'[Year])

var _vtable_rate = ADDCOLUMNS('Table (38)',"Rate",

var _currency = 'Table (38)'[Curency] RETURN

CALCULATE(MAX('Table (39)'[Rate]),_currency='Table (39)'[From Currency],'Table (39)'[To Currency]="EUR"))

RETURN

SUMX(_vtable_rate,

[Rate]*'Table (38)'[Amount])

Here I added SUMX and virtual table to account for empty filter context.

[sunyangjun1115]

Hi ValtteriN, many thanks for your time and support. it works now. really appreciated .