cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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. 

 

Exchange Rate Table.PNG

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. 

 

Thanks again in advance, 

 

Josh

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

Hi @Joshua_Peter,

 

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
albert5312 Frequent Visitor
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.

Community Support Team
Community Support Team

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

Hi @Joshua_Peter,

 

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.