summation of currency not working

[NarreN]

Hi!

I have a table with AgreementLines all in local currency.

Example:

I have currencyRate table to be able to make a measure to calculate the currency of each row to NOK.

I made a measure on my AgreementLine Table called "Total NOK":

Total NOK =
VAR
    _currency = MAX(AgreementLine[Cur] )
VAR
    _conversionRate =
        CALCULATE(
            MAX( CurrencyRate[Rate] )
            ,CurrencyRate[TransactionCurrency] = _currency
            )
VAR
    _TotalInNok =
        SUM(AgreementLine[Total]) * _conversionRate
RETURN
    _TotalInNok

This works very well and the numbers are correct per row in my AgreementLine table.

The problem is if I want to sum the field "Total NOK" in a table view or Card. 

TableView will end up like this:

The correct sum for "Total NOK" would be: 39 432,53.

I understand the logic, but what I want to achieve is to SUM each row of "Total NOK" which is already calculated.

What is the best approach to deal with local currency and get this setup for my reports?

[johnt75 · Accepted solution]

Create a one-to-many relationship from your currency rate table to your agreement line table then create a measure like

Total NOK =
SUMX (
    AgreementLine,
    AgreementLine[Total] * RELATED ( 'Currency Table'[Rate] )
)

[NarreN]

Thank you! 
This worked like a charm! 🙂