Forum Discussion

CR205664's avatar
CR205664
New Member
7 years ago

Converting Multiple Currencies into One Currency

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?

 

2 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    7 years ago

    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. 

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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