Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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
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.
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |