FX Variance calculation - SUMX doesn't work

[Dan80]

Hi all,

I need to conduct variance analysis on some financials including the impact of changes in fx rates.  The variance calculation is:

Actual Revenue_Base Currency - (Actual Revenue_Local Currency / Budget Fx Rate)

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'.  To do this, I used SUMX e.g.

SUMX(Table, Actual Revenue_Base Currency) - (SUMX(Table, Actual Revenue_Local Currency) / (SUMX(Table, Budget Revenue_Local Currency)/SUMX(Table, Budget Revenue_Base Currency))

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.  A simple example is below, would appreciate any help.  Thanks,

Dan

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

[Greg_Deckler]

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]

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

[Greg_Deckler]

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]

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]

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 🙂

[Greg_Deckler]

I'm apparently still missing some piece to this puzzle. I started with an Enter Data query of your data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipNSU8tUdJRCg12BJJmBgZA0tAATBnomQFJpVgdJGWuoUFA0hQsb2kKUWVqDFPmmFxSmpgDN80ErMwCapgpkFQxNDbWMzZWQFUNMRSiztAIZrc5kNJVMTYy0AOKxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Version = _t, Region = _t, #"Total Sales Local Currency" = _t, #"Total Sales Base Currency" = _t, #"Fx Rate" = _t, #"Fx Var" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", type text}, {"Region", type text}, {"Total Sales Local Currency", Int64.Type}, {"Total Sales Base Currency", Int64.Type}, {"Fx Rate", type number}, {"Fx Var", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Fx Rate", "Fx Var"})
in
    #"Removed Columns"

Basically:

VersionRegionTotal Sales Local CurrencyTotal Sales Base Currency

VersionRegionTotal Sales Local CurrencyTotal Sales Base Currency

Budget	USA	600	1000
Budget	EUR	500	950
Actual	USA	400	800
Actual	EUR	800	1200

Then I did your calculations as measures:

Actual Revenue Base Currency = CALCULATE(SUMX(Currencies, [Total Sales Base Currency]),Currencies[Version]="Actual")

Actual Revenue Local Currency = CALCULATE(SUMX(Currencies, [Total Sales Local Currency]),Currencies[Version]="Actual")

Budget Revenue Base Currency = CALCULATE(SUMX(Currencies, [Total Sales Base Currency]),Currencies[Version]="Budget")

Budget Revenue Local Currency = CALCULATE(SUMX(Currencies, [Total Sales Local Currency]),Currencies[Version]="Budget")

The only way I could come up with your numbers for Fx Rate was to use a Calculated Column:

Fx Rate = DIVIDE([Total Sales Local Currency],[Total Sales Base Currency])

Then I created a measure:

Fx Variance = [Actual Revenue Base Currency]-(DIVIDE([Actual Revenue Local Currency], SUM([Fx Rate])))

Now I put this into a Matrix with Version and Region and came up with:

Which sort of looks right (other than totals obviously) but I don't feel like I arrived at your numbers the way you are doing it so I am obviously missing something. So before trying to get to the right answer, I want to know if this path looks correct or if I am still missing some piece of the puzzle.

[Dan80]

Ding Dong smoupre - thanks for taking so much time on this, appreciate it.  You are spot on re how my model is currenlty working, when I break it down to the most granular levels the variances are exactly the same as what you have, it is just that I need the totals to make sense as well.  Let me know if I can clarify anything else - thanks!

dan