FX Variance calculation - SUMX doesn't work

Question

Hi all,&nbsp;I need to conduct variance analysis on some financials including the impact of changes in fx rates.&nbsp; The variance calculation is:&nbsp;Actual Revenue_Base Currency - (Actual Revenue_Local Currency / Budget Fx Rate)&nbsp;This is fine when there is only 2 currencies, however, in my data there is 10 different currencies and as a result the above calculation needs to be done at an individual row level and then 'summed up'.&nbsp; To do this, I used SUMX e.g.&nbsp;SUMX(Table, Actual Revenue_Base Currency) - (SUMX(Table, Actual Revenue_Local Currency) / (SUMX(Table, Budget Revenue_Local Currency)/SUMX(Table, Budget Revenue_Base Currency))&nbsp;Results are correct at an individual currency level, however, the total is completey wrong as it appears that for the grand total is recalculating the variance as opposed to 'summing up' the individual parts.&nbsp; A simple example is below, would appreciate any help.&nbsp; Thanks,&nbsp;Dan&nbsp;VersionRegionTotal Sales Local CurrencyTotal Sales Base CurrencyFx RateFx Var&nbsp;BudgetUSA60010000.60&nbsp;&nbsp;BudgetEUR5009500.53&nbsp;&nbsp;ActualUSA4008000.50$133.33-$186.67ActualEUR80012000.67-$320.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Budget&nbsp;Summed110019500.56&nbsp;&nbsp;Actual&nbsp;Summed90017500.51$154.55&nbsp;

greg_deckler · Answer

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?

dan80 · Answer

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 &amp; EUR variances. Thanks

greg_deckler · Answer

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.

greg_deckler · Answer

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?

dan80 · Answer

Hi, sorry for the confusion, not easy to explain, the list of measures used are:&nbsp;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")&nbsp;Budget&nbsp;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])&nbsp;Then I reference the above in the variance calculation, as per:&nbsp;Fx Variance:=[Actual Revenue Base Currency]-(DIVIDE([Actual Revenue Local Currency], [Budget Fx Rate]))&nbsp;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'.&nbsp; Really appreciate your help and time.&nbsp; If you crack this I will buy you a beer :)&nbsp;

Forum Discussion

FX Variance calculation - SUMX doesn't work

7 Replies

Related content

Variance calculation dex

Calculate average variance

Dynamic Format DAX measure calculating variance

Calculation Groups, Variances, Slicers

Calculating Variance

Version	Region	Total Sales Local Currency	Total Sales Base Currency	Fx Rate	Fx Var
Budget	USA	600	1000	0.60
Budget	EUR	500	950	0.53
Actual	USA	400	800	0.50	$133.33	-$186.67
Actual	EUR	800	1200	0.67	-$320.00

Budget	Summed	1100	1950	0.56
Actual	Summed	900	1750	0.51	$154.55