Forum Discussion
FX Variance calculation - SUMX doesn't work
Are you doing this as a measure? If so, the Total line in a table can be not what is expected because the Total link calculates not upon what is in the table but rather looking at all of the data (essentially removes the row level context. Generally this is able to be fixed with some additional DAX work. Can you post some mock data and then your expected result?
- Thanks for your reply and yes, I am trying to create a measure. The top 4 rows of the table in my original post is an example of the data, using these numbers I would expect the answer to be -$186.67 ie the sum of USA & EUR variances. Thanks
OK, thanks for explaining your data, I'll try to recreate. In the meantime, check out my DAX tip "Dealing with Measures" here:
http://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/m-p/63376#U63376
It focuses on this exact problem.
OK, I think I am missing some pieces of this. It seems like you have some intermediary measures involved here like "Actual Revenue_Base Currency", etc. Can you post those formulas? And, I am assuming that the formula you posted is for Fx_Var, correct? Also, are you missing in ")" in the formula you posted?
