Forum Discussion

PBI-Newbie's avatar
PBI-Newbie
Helper I
2 years ago
Solved

Problems with Exchange Rate calculation - especially when aggregating

I have a built a measure that calculates the FX Rate as following: Fx Rate= CALCULATE( SUMX('ExchangeRates','ExchangeRates'[Exchange_Rate]), STARTOFYEAR('Calendar'[Date])   This changes depending...
Need Help
  • PBI-Newbie's avatar
    PBI-Newbie
    2 years ago

    I've found a solution after all. I put the lookupvalue in the Sumx calculation instead of splitting them in two. The calculation then kept the row context.

PBI-Newbie's avatar
PBI-Newbie
Helper I
2 years ago

Thanks Anonymous , unfortunately that's not working as the measure is running into memory problems then. I am pulling all SAP entries (tenthousands of lines) via direct query. 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi PBI-Newbie ,

 

Please try:

New Measure =
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Company],
        "total",[Fx Rate]
    ),
    [total]
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

  • PBI-Newbie's avatar
    PBI-Newbie
    Helper I
    2 years ago

    thanks Anonymous . Unfortunately this didn't resolve my issue. The total is still wrong as it is not the sum of each row, but the sum of all Sales divided by the Sum of all FX Rates. I feel that I am missing something important, but can't put my finger on it. 
    Would it maybe be better to incorporate the lookup function in the Sales calculation and match the company codes and the FX rate?