Converting Multiple Currencies into One Currency

[CR205664]

My lookup Table has about 9 currencies and my Primary Key is the colum that has them identified by 3 letters (USD,JPY,EUR).  My Data table also has the Currency field in the same 3 letters. This is how i've related my 2 tables.  

I thought a measure would be better than a calculated column - but I'm struggling with a way to relate these two tables.  Old School Excel is just a VLookUP  but when i try to write DAX with "related" i get no where. 

Fx Lookup Table
USD 1.0
JPY 0.0091
EUR 1.174

Data Table
Currency  Total Paid
USD          2.0
JPY            1500.5
EUR           25

How do i see my total PAID in USD for my entire Data Table?

[v-piga-msft]

Hi @CR205664,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your data sample as table format and your desired output so that we could help further on it?

Best  Regards,

Cherry

[MattAllington]

If you already have a relationship between the tables, you don’t need lookupvalue. 

Set up a matrix

place currency from the fx lookup table (not the data table) in the matrix on rows. 

Add a measure: base currency value = sum(data[value]) to the matrix

add another measure: converted value = sumx(fxTable,fxTable[rate] * [base currency value])

there is a lot going on in this formula - the apparent simplicity belies the complexity under the hood.