cancel
Showing results for
Did you mean:
Member

## Calculated column returning a single value based on filters from two other columns

Hi,

Another hopefully Quick QU...

I have a table of exchange rates and another table of sales that contain a Currency ID, for example, USD, GBP, EUR and a Transaction Date that contains the Year which corresponds with the year in the exchanges rate table.

What I want to do is create a calculated column where it pulls in the exchange rate based on the Currency ID in the sales table and the Transaction Year.

Then on the sales data table, there is the transaction column and a currency column which matches the currency ID table pictured

What I have pictured is almost like an if statement

If SalesTable[CurrencyID]=ExchangeRate[CurrencyID] && SalesTable[TransactionDate].Year=ExchangeRate[Year].Year return the ExchangeRate[Value]

Seems so simple but is throwing me a massive spanner in the works.

Ultimately this is to turn a multiple currency reports into a single \$USD Value report using the exchange rate average from the same year as the transaction date year.

Josh

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## Re: Calculated column returning a single value based on filters from two other columns

First, add a calculated column in exchange rate table to get the average of exchange rate per year per currency.

```Average rate =
CALCULATE (
AVERAGE ( ExchangeRate[Rate] ),
ALLEXCEPT (
ExchangeRate,
ExchangeRate[CurrencyID],
ExchangeRate[Year]
)
)```

Then, add a calculated column in sales table to return the proper rate value which matches the Currency ID and Transaction Year.

```Rate =
LOOKUPVALUE (
ExchangeRate[Average rate],
ExchangeRate[CurrencyID], SalesTable[CurrencyID],
ExchangeRate[Year], SalesTable[TransactionDate].YEAR
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Frequent Visitor

## Re: Calculated column returning a single value based on filters from two other columns

maybe by creating a new dax measure and giving each type of currency ID a certain value, you can then create your argument for the data you want to present.

Highlighted
Community Support Team

## Re: Calculated column returning a single value based on filters from two other columns

First, add a calculated column in exchange rate table to get the average of exchange rate per year per currency.

```Average rate =
CALCULATE (
AVERAGE ( ExchangeRate[Rate] ),
ALLEXCEPT (
ExchangeRate,
ExchangeRate[CurrencyID],
ExchangeRate[Year]
)
)```

Then, add a calculated column in sales table to return the proper rate value which matches the Currency ID and Transaction Year.

```Rate =
LOOKUPVALUE (
ExchangeRate[Average rate],
ExchangeRate[CurrencyID], SalesTable[CurrencyID],
ExchangeRate[Year], SalesTable[TransactionDate].YEAR
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.