Exchange rates

[Anonymous]

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?

[Anonymous]

Hello Mr. Yolo, 

perfect it works fine. Thanks for the help

[v-xinruzhu-msft · Accepted solution]

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]*a

2.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]*a

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.