Fx rate from another table does not calculate right

[TheBaz_57]

Hi community

I need your help one more time. 

I have a report with one table that looks like the below:

Instrument	Used
	1,000
EUR	3,500
USD	1,700
SGD	600
	2,000

Another table (fictive rate)

Currency	Fx rate from EUR
USD	1
SGD	0.5

So what I want to have a new column in the first table that gives the amount converted into EUR except when instrument is blank or EUR.

I have made a relationship between the 2 tables with Instrument linked to Currency. 

When calculating the new column I have tried:

Nw column = Sum('Table1'[Used])*Sum('Table2'[Fx rate from EUR])

But doesn't work. 🙄

I missed something most probably. 

If you can help would be really appreciated. 

[v-yangliu-msft · Accepted solution]

Hi  @TheBaz_57 ,

Here are the steps you can follow：

1. Create calculated column.

Nw column =
var _fx=CALCULATE(SUM('Table2'[Fx rate from EUR]),FILTER(ALL(Table2),'Table2'[Currency]=EARLIER('Table1'[Instrument])))
return
'Table1'[Used] * _fx

2. Result:

Best Regards,

Liu Yang

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