Forum Discussion
Exchange rates
Hello Community,
I have a small question regarding the exchange rates. I have columns named rate_1, Rate_2,Rate_3 and Rate_4 in one source all these rates are in different currencies and now i have another source/table with exchange rates table with Currrecny, Exchange rate as columns. how can i identifc the currenies from 1st table and then multiply according with other table which has exchange rates values for all currencies?
R-Table_1
Rate_1 Rate_2 Rate_3 Rate_4 Currency
10 20 30 40 AUD
15 25 35 60 USD
and so on in multiples currencies
R-Table_2
Excahnge rate Currency
0.8 AUD
0.95 USD
Now all the values of 10, 20, 30, 40 should be multiplies by0.8 as here its 0.8 for AUD and 15, 25, 35, 60 with 0.95 as here its USD
Could anyone helpme in writing DAX formula for this please?
Hi Anonymous
1.The best suggestion that you can unpivot the table1 first
Put the following code in Advanced Editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyAhHGIMIERDiGuijF6gDlTEFyIMIYRJiB5EKDgXKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rate_1 = _t, #"Rate_2#(lf)Rate_1#(lf)" = _t, #"Rate_3#(lf)" = _t, #"Rate_4#(lf)" = _t, Currency = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rate_1", Int64.Type}, {"Rate_2#(lf)Rate_1#(lf)", Int64.Type}, {"Rate_3#(lf)", Int64.Type}, {"Rate_4#(lf)", Int64.Type}, {"Currency", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Rate_2#(lf)Rate_1#(lf)", "Rate_2"}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Currency"}, "Attribute", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "RateType"}}) in #"Renamed Columns1"Then apply the table, and create a calculated column in table
Rates = var a=LOOKUPVALUE('R-Table_2'[Exchange_rate],[Currency],'R-Table_1 (2)'[Currency]) return [Value]*a2.You can create four calculated columns in table1
e.g calculate the rate of rate 1
Column = var a=LOOKUPVALUE('R-Table_2'[Exchange_rate],[Currency],'R-Table_1'[Currency]) return [Rate_1]*aBest Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous
1.The best suggestion that you can unpivot the table1 first
Put the following code in Advanced Editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyAhHGIMIERDiGuijF6gDlTEFyIMIYRJiB5EKDgXKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rate_1 = _t, #"Rate_2#(lf)Rate_1#(lf)" = _t, #"Rate_3#(lf)" = _t, #"Rate_4#(lf)" = _t, Currency = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rate_1", Int64.Type}, {"Rate_2#(lf)Rate_1#(lf)", Int64.Type}, {"Rate_3#(lf)", Int64.Type}, {"Rate_4#(lf)", Int64.Type}, {"Currency", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Rate_2#(lf)Rate_1#(lf)", "Rate_2"}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Currency"}, "Attribute", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "RateType"}}) in #"Renamed Columns1"Then apply the table, and create a calculated column in table
Rates = var a=LOOKUPVALUE('R-Table_2'[Exchange_rate],[Currency],'R-Table_1 (2)'[Currency]) return [Value]*a2.You can create four calculated columns in table1
e.g calculate the rate of rate 1
Column = var a=LOOKUPVALUE('R-Table_2'[Exchange_rate],[Currency],'R-Table_1'[Currency]) return [Rate_1]*aBest Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.