4 Replies Latest reply on Mar 9, 2012 7:47 AM by Tracy Rodgers

# How to sum calculated values?  Tried WINDOW_SUM and TOTAL but did not work

Hello,

We have records for which we need to:

(1) Calculate the ABS(SUM([Field])) this is working.  i.e. If we have -400 and 300 then this totals -100.  We want the total to display as +100.

(2) We want the results of step (1) to be summed.  We have bene unable to get this to work.  We have tried WINDOW_SUM and TOTAL.

Sample attached.

Expected totals are:

Row level:

ABC - 400

DEF - 600

JHK - 450

Column level:

EUR - 850

USD - 600

Any help greatly appreciated.

Thanks,

Dan

• ###### 1. Re: How to sum calculated values?  Tried WINDOW_SUM and TOTAL but did not work

Hi Dan,

The reason that the grand totals are not returning as expected is because totals use whatever aggregation is on the measure. It is not necessarily a sum of whatever is in the row or column. Therefore, since the sum is being applied first to Amount, the Amount is being summed and then the absolute value is added. By adding the window_sum or running_sum the last value in the row/column will be correct, however, the grand total will revert to the aggregate on the measure.

Currently, the best solution is to get the desired grand total on one worksheet and the desired details on a separate worksheet and put the two on a dashboard together.

Hope this helps!

-Tracy

• ###### 2. Re: How to sum calculated values?  Tried WINDOW_SUM and TOTAL but did not work

Thanks Tracy!

• ###### 3. Re: How to sum calculated values?  Tried WINDOW_SUM and TOTAL but did not work

By using partitioning and table calculations, you are able to achieve what you are looking for, see the attached.

What do you think of this route Tracy?

• ###### 4. Re: How to sum calculated values?  Tried WINDOW_SUM and TOTAL but did not work

That's an awesome solution! I've never thought of that...thanks Joe!