how to sum calculated absolute values

[torpe999]

Hello,

I am trying to sum calculated absolute values but not successfully.  I need to calculate total demand accuracy which is

= ( 1- sum (calculated absolute gap deviation)/ sum(planned volume) 

but when I try to sum calculated absolute gap deviation, i get sum of gap deviation but not sum of absolute gap deviation though in the table total field it's shown correctly. What should i do in order to sum absolute values and get correct total demand accuracy?

Produkto Nr.	Parduota (sold)litru	Suplanuota (planned)litru	abs gap dev	gap dev
	40 320	25300	15740	15020
S00132	8 640	9000	360	-360
S05301	5 760	4300	1460	1460
S05303	25 920	12000	13920	13920

Total demand accuracy

0,406324111

and it should be:  should be 1-(15740/25300)=0,377

[chrismarlow · Accepted solution]

Hi,

You need to use AGGR wrapped around the bit you taking the absolute value to make it apply to each row, even in the total, so I think something like this will work, but will depend on how your totals have come together;

1-(sum(aggr(fabs(sum([Parduota (sold)litru])-sum([Suplanuota (planned)litru])),[Produkto Nr.]))/sum([Suplanuota (planned)litru]))

Cheers,

Chris.

[torpe999]

great, thanks! It worked!