Converting Revenue according to a set quarterly exchange rate for multiple currencies

[Anonymous]

Hi,

Hope im posting this to the right channel!

I have two excel based dataset

Dataset 1: Quarterly USD exchange rate table

• A list of set currencies that are used to calculate sales in various currencies during a quarter, to USD

Quarter	Local CCY	USD xrate
4-2020	SEK	9.700
4-2020	NOK	9.120
4-2020	DKK	6.100
4-2020	EUR	0.799
4-2020	USD	1.000
3-2020	SEK	9.200
3-2020	NOK	9.620
3-2020	DKK	6.100
3-2020	EUR	0.911
3-2020	USD	1.000

Dataset 2: Sales data in a table

• The data itself is much more complex and has customer details etc, but the rows always have these values

Quarter	Local CCY	Total sales (local CCY)
4-2020	SEK	5,114,999
4-2020	NOK	4,105,999
4-2020	DKK	2,116,999
4-2020	EUR	111,999
4-2020	USD	113,999
3-2020	SEK	4,113,999
3-2020	NOK	2,13,999
3-2020	DKK	1,113,999
3-2020	EUR	113,999
3-2020	USD	113,999

End result I would want to produce in Table visualisation in POWER BI:

• "Total sales (USD)" column would be filled with the conversion result using the matching quarter to a matching Local Currency --> "Total sales (Local CCY)" divided by "USD xrate"

Quarter	Local CCY	Total sales (local CCY)	Total sales (USD)
4-2020	SEK	5,114,999
4-2020	NOK	4,105,999
4-2020	DKK	2,116,999
4-2020	EUR	111,999
4-2020	USD	113,999
3-2020	SEK	4,113,999
3-2020	NOK	2,13,999
3-2020	DKK	1,113,999
3-2020	EUR	110,999
3-2020	USD	116,999

Thank you very much for your help in advance.

[v-diye-msft]

Hi @Anonymous 

If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!

If not, please kindly elaborate more.

[Greg_Deckler · Accepted solution]

@Anonymous - Well, you could either create a calculated column or measure like this:

Calculated Column = DIVIDE('Dataset 2'[Total sales (local CCY)],LOOKUPVALUE('Dataset 1'[USD xrate],'Dataset 1'[Quarter],'Dataset 2'[Quarter],'Dataset 1'[Local CCY],'Dataset 2'[Local CCY]),0)

Or you could create a concatenated column in both tables, [Quarter] & "-" & [Local CCY] and form a relationship between them and then you could just use RELATED or RELATEDTABLE in your calculation instead of LOOKUPVALUE.