Multiple Currencies in a single column converted to Euro or Dollar

[ymirza]

I have this report with Cost of Assets purchased using different currencies. I would like to have one Column which will automatically convert them to Euro rate from another table. What is the appropriate measure?

Currency	Cost
YTL	19,571.79
USD	5,990.60
MAD	0.00
YER	126,000.00
USD	2,314.55
SAR	5,500.00
MAD	6,800.00
NGN	50,193.20
KES	638,812.35
YER	35,750.00
USD	8,329.00
SAR	5,500.00

Currency	Rate
DZD	142.9654
EGP	23.83123
EUR	1
IRR	155989.4
JOD	0.858257
KES	130.0549
KWD	0.361328
LBP	1815.732
MAD	11.70195
NGN	484.6802
PKR	153.499
SAR	4.497835
SEK	10.48924
TND	3.745863
YTL	9.394526
AED	4.400503
GBP	0.90639
USD	1.197867
YER	300.6269

[jdbuchanan71 · Accepted solution]

Hello @ymirza 

You would join the two tables on the currency field then this measure will get you the converted cost.

Cost EUR = SUMX ( Assets, Assets[Cost] * RELATED( 'Exchange Rates'[Rate] ) )

[ymirza]

It is returning huge number. Probably it is adding to the SUMX equation?

Measure used: 

Cost Eur = SUMX(Data,Data[Cost]*RELATED(Rates[Rate]))

Even tried this:

Cost Eur = SUMX(Data,Data[Cost] / RELATED(Rates[Rate]))

returns

[jdbuchanan71]

Do you have the two tables joined on the currecy field?

This is what your sample data looks like for me.

[ymirza]

Yes the relationship is already established between two currency tablesstill returns a huge number. the format is Whole Number

[jdbuchanan71]

If you load your two sample tables into a .pbix do you see the same numbers I see with the measure?

Are you able to share your .pbix file?  I can't think of what would cause the measure to behave that way.

[ymirza]

the format of the column was the problem. It loaded as Whole number from Excel. When I changed it to Decimal Number in Query Editor your solution worked. thanks