FX Conversion: Directionality using IF statements

[asmirnoffnorth]

Hi all,

I hope everyone had a good New Year and are keeping safe. I am new to PowerBi and am slowly learning the ropes.

I was hoping someone could assist me in a nested IF statement to calculate a value. I have the following datatables:

FX: this stipulates the currency information and most importantly the direction of a certain currency (vs the USD)

ID	NAME	INVERSE
1	AUD	1
2	BRL	0
3	CAD	0
4	EUR	1
5	GBP	1
6	JPY	0
7	MXN	0
8	RUB	0
9	USD	0

FXDATA: this hold the fx exchange rate data by different roots (sources) depending on the time of day

ID	ROOTID	FXID	VALUE	DATE
1	1	1	0.7569	30/12/2020
2	-100	1	0.78965	30/12/2020
3	1	2	5.2684	30/12/2020
4	-100	2	5.2453	30/12/2020
5	1	3	1.285364	30/12/2020
6	-100	3	1.27987	30/12/2020
7	1	4	1.21785	30/12/2020
8	-100	4	1.2087	30/12/2020
9	1	5	1.35687	30/12/2020
10	-100	5	1.36798	30/12/2020
11	1	6	103.69548	30/12/2020
12	-100	6	103.42365	30/12/2020
13	1	7	19.64	30/12/2020
14	-100	7	20.45	30/12/2020
15	1	8	63.5774	30/12/2020
16	-100	8	64.235	30/12/2020
17	1	9	1	30/12/2020
18	-100	9	1	30/12/2020

SALES: this holds items and their local amounts.

ID	DATE	ITEM	AMOUNT	FXID
1	30/12/2020	item 1	164,896,524.00	8
2	30/12/2020	item 2	203,857,678.00	8
3	30/12/2020	item 3	68,078.97	8
4	30/12/2020	item 4	30,980.42	1
5	30/12/2020	item 5	7,594.52	5
6	30/12/2020	item 6	91,115.50	7
7	30/12/2020	item 7	95,427.76	4
8	30/12/2020	item 8	12,661.95	3
9	30/12/2020	item 9	17,211.14	6
10	30/12/2020	item 10	59,489.09	9
11	30/12/2020	item 6	10,796.61	2
12	30/12/2020	item 9	71,384.18	5
13	30/12/2020	item 3	39,691.65	9
14	30/12/2020	item 6	86,725.45	5
15	30/12/2020	item 3	9,564.14	3
16	30/12/2020	item 8	88,367.64	6
17	30/12/2020	item 10	97,405.52	4
18	30/12/2020	item 1	27,168.34	8
19	30/12/2020	item 3	43,512.21	3

On the Report view I use the following filters:

Sales.Date - self explanatory

FXDATA.RootID - tell the system which FX rate to use (given there are 2 values saved on a day per currency)

With those filters on, I want to calculate the USD amount for a certain Item (found in the SALES tab).

So essentially depending on the currency for that item, the system will look through the INVERSE column to figure out the direction of the calculation: if it is 0 then divide the local amount by the specific FX rate; if 1 multiply the local amount by the specific FX rate.

Changing the RootID filter should then yield a different result as the Report would use the (albeit slightly) different FX amount in its calculation. 

Expected Result

ID	DATE	ITEM	AMOUNT	FXID	Step 1: What is FX rate for root ID and date selected	Step 2: FX direction inverse?	Calculated USD amount
1	30/12/2020	item 1	164,896,524.00	6	103.42365	0	1,594,379.27		Root ID	-100
2	30/12/2020	item 2	203,857,678.00	6	103.42365	0	1,971,093.44		Date	30/12/2020
3	30/12/2020	item 3	68,078.97	9	1	0	68,078.97
4	30/12/2020	item 4	30,980.42	1	0.78965	1	24,463.69
5	30/12/2020	item 5	7,594.52	5	1.36798	0	5,551.63
6	30/12/2020	item 6	91,115.50	7	20.45	0	4,455.53
7	30/12/2020	item 7	95,427.76	4	1.2087	0	78,950.74
8	30/12/2020	item 8	12,661.95	3	1.27987	0	9,893.15
9	30/12/2020	item 9	17,211.14	6	103.42365	0	166.41
10	30/12/2020	item 10	59,489.09	9	1	0	59,489.09
11	30/12/2020	item 6	10,796.61	2	5.2453	0	2,058.34
12	30/12/2020	item 9	71,384.18	5	1.36798	0	52,182.18
13	30/12/2020	item 3	39,691.65	8	64.235	0	617.91
14	30/12/2020	item 6	86,725.45	4	1.2087	0	71,751.01
15	30/12/2020	item 3	9,564.14	8	64.235	0	148.89
16	30/12/2020	item 8	88,367.64	6	103.42365	0	854.42
17	30/12/2020	item 10	97,405.52	4	1.2087	0	80,587.01
18	30/12/2020	item 1	27,168.34	8	64.235	0	422.95
19	30/12/2020	item 3	43,512.21	3	1.27987	0	33,997.37

If directionality wasn't an issue i would create a New Measure and use the SUM to take the value of the SALES.VALUE and / by SUM of the FXDATA.VALUE.

Any help will be greatly appreciated.

[asmirnoffnorth]

 Many thanks @jparr that has worked. i really do appreciate your prompt response!

[parry2k · Accepted solution]

@asmirnoffnorth see attached, in your post the output table you showed seems to be showing wrong data, anyhow check attached.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡