Problems with Exchange Rate calculation - especially when aggregating

[PBI-Newbie]

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 on the company and year selected in the slicer.

It all works well if individual companies are selected or companies with the same currency, but the aggregation never seems to work 

Example: 

Company	Sales USD
A	4 501 342
B	22 773 767
Sum	15 147 088

It also doesn't work when I bring in products and look at multiple countries. 

Sales USD measure = [Sales]*[Fx Rate]

Sales measure = SUMX(
Filter('Transactions','Transactions'[GL]="0040000"||'Transactions'[GL]="0041000"),
'Transactions'[Amount]*-1)

I cannot post the Pbix as it's company data. 

What am I missing? Why is the aggregation not working for multiple countries or for products?

[PBI-Newbie · Accepted solution]

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.

[Anonymous]

Hi @PBI-Newbie ,

Please try update measures.

Fx Rate= CALCULATE(
SUMX(ALLSELECTED('ExchangeRates'),'ExchangeRates'[Exchange_Rate]), STARTOFYEAR('Calendar'[Date])

Sales measure = SUMX(
Filter(ALLSELECTED('Transactions'),'Transactions'[GL]="0040000"||'Transactions'[GL]="0041000"),
'Transactions'[Amount]*-1)

And then create new measures to be displayed on the visual object instead of the old ones, when the total should be correct.

NewMeasure = SUMX(VALUES(Table[ROWS]), [YourCurrentMeasure])

If the above one can't help you get the desired result, please detail your logic, input data and output results, and if you can provide a pbix file with dummy data that would be great.

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]

Hi @Anonymous , thanks for the pointers. 
I don't understand the third part. Why is there a need for a third measure? And what's the Table[Rows] supposed to be? similarly - is the [YourCurrentMeasure] the first new FX measure or is there a need to sum the Sales again?

Thanks!

[Anonymous]

Hi @PBI-Newbie ,

I mean when you place the measures [Fx Rate], [Sales measure] on the visual object if his totals are not correct, you can create another measure instead of the old one and place it on the visual object.

like this:

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]

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]

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]

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? 

[Anonymous]

Hi @PBI-Newbie ,

Checkout this article

DAX Patterns: Currency conversion - SQLBI

Cheers
CheenuSing