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?
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?
Hi, sorry for the confusion, not easy to explain, the list of measures used are:
Actual Revenue Base Currency:=CALCULATE(SUMX(Table, Total Revenue Base Currency),Version="Actual")
Actual Revenue Local Currency:=CALCULATE(SUMX(Table, Total Revenue Local Currency),Version="Actual")
Budget Revenue Base Currency:=CALCULATE(SUMX(Table, Total Revenue Base Currency),Version="Budget")
Budget Revenue Local Currency:=CALCULATE(SUMX(Table, Total Revenue Local Currency),Version="Budget")
Budget Fx Rate:=DIVIDE([Budget Revenue Local Currency], [Budget Revenue Base Currency])
Then I reference the above in the variance calculation, as per:
Fx Variance:=[Actual Revenue Base Currency]-(DIVIDE([Actual Revenue Local Currency], [Budget Fx Rate]))
The example used stripped out other complications, such as month and product so I need the calculation to be completed at the lowest level and then 'Summed up'. Really appreciate your help and time. If you crack this I will buy you a beer :)