Forum Discussion
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 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?
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.
8 Replies
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.
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!
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.
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.
