Variance calculation

[Mahadevaraobc]

Hi Everyone,

Im having difficulty in achieving the following, please help.



I have created a Table visual as below.



Country Serial No actual budget diff %
USA ? 89 78 -11 -14.10%
USA AA 7 6 -1 -16.67%
USA AB 2 3 1 33.33%
USA BC 12 7 -5 -71.43%
USA DD 453 461 8 1.74%
USA FF 196 216 20 9.26%
USA GG 183 170 -13 -7.65%
USA HH 12 14 2 14.29%
USA II 1 1 0 0.00%
USA JJ 10 7 -3 -42.86%
USA KK 210 247 37 14.98%
USA LL 146 146 0 0.00%
USA MM 4067 4093 26 0.64%
USA NN 1 1 0 0.00%
canada ? 1045 918 -127 -13.83%
canada AA 1 5 4 80.00%
canada AB 86 40 -46 -115.00%
canada BC 1999 1899 -100 -5.27%
canada DD 4 2 -2 -100.00%


What i want is:

1) When no county is selected all serial number values should be summed up and show diff and diff in %

2) if any country is selected only serial numbers to that country should be summed and diff and diff in % should be shown

[MattAllington · Accepted solution]

I assume the % is the issue. You will need SUMX for the diff column, then the % will work. 

https://exceleratorbi.com.au/use-sum-vs-sumx/

the complication is you have 2 columns that define the unique list (country and serial).  The correct formula depends on you table structure.