Sum of Variances by Item

[mmarra]

I am currently attempting to review a set of data related to variances in inventory counts. A very simple example of the data is as follows;

Count #        Item            Variance
       1              ABCD                  -3
       2              ABCD                   2
       2              ABCD                  -4
       2              EFGH                   3

Here is the problem I am having. I have created a table and formulas to sum the variances by Count # as well as the absolute value of those variances. What I would like to present is the absolute variance of the variance by item. As of now, the only way I have come up with to complete this is to use the source data and create a table with the count and item as dimensions and sum the relevant fields, then export the file and load it into a new app and re-perform the analysis. For reference, the new file based on my example data above looks like this:

Count #        Item            Variance          Absolute Variance
       1              ABCD                  -3                          3
       2              ABCD                  -2                          2
       2              EFGH                   3                           3

My question is can I calculate the absolute value of the variance amounts by using the combination of count # and item # in a formula without . I'll likely need to use the "FABS(SUM(" with some type of expression, just need someone to help me with it if possible. Thanks in advance for any help you can give. 

[CELAMBARASAN · Accepted solution]

You can prepare this data in the backend itself in the same app

SourceData:

Load Count#, Item, Variance from Source_file;

Transform:

Load Count#, Item, Sum(Variance) as Variance, Fabs(Sum(Variance)) as Absolute_Variance

Resident SourceData

Group by Count#, Item;

Drop Table SourceData;

or in the front end try in the combination of Aggr, sum and fabs

Example:

Sum(Aggr(Fabs(Sum(Variance)), Count#, Item)) 

[mmarra]

Thank you Celambarasan, your "front end" solution worked perfectly and allowed me to keep the full analysis over the original data while adding analysis around the consolidated numbers. I truly appreciate your help and thank you for your time.