Forum Discussion

Elmarie_Kies's avatar
Elmarie_Kies
Frequent Visitor
2 years ago
Solved

Summing converted amount

Hi there,    I have a local currency, exchange rate and USD measures. Local currency / Exchange rate = USD. On a year level it should not use the 17.60 as this is an average of the two months. The...
Need Help
  • MFelix's avatar
    MFelix
    2 years ago

    Hi Elmarie_Kies ,

     

    In this case you need to do a different calculation use the following code:

    USD = SUMX(SUMMARIZE('Table', 'Calendar'[Year], 'Calendar'[MonthNo]), CALCULATE(DIVIDE(SUM('Table'[Local]), AVERAGE('Table'[Echange Rate]))))

    I'm assuming you have a calendar table for the dates:

    Don't mind the Exchange rate because using the column directly.

MFelix's avatar
MFelix
Super User
2 years ago

Hi Elmarie_Kies ,

 

In this case you need to do a different calculation use the following code:

USD = SUMX(SUMMARIZE('Table', 'Calendar'[Year], 'Calendar'[MonthNo]), CALCULATE(DIVIDE(SUM('Table'[Local]), AVERAGE('Table'[Echange Rate]))))

I'm assuming you have a calendar table for the dates:

Don't mind the Exchange rate because using the column directly.

Elmarie_Kies's avatar
Elmarie_Kies
Frequent Visitor
2 years ago

Works like a charm! Thank you very much MFelix.