Forum Discussion
Problems with Exchange Rate calculation - especially when aggregating
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.
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.
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.