cancel
Showing results for
Did you mean:
Frequent Visitor

## Fx rate from another table does not calculate right

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.

1 ACCEPTED SOLUTION
Community Support

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

Community Support

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

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors