4 Replies Latest reply on Apr 14, 2018 6:22 AM by Mike Mainzer

# Need to perform SUM on this calculated Field . Sum is disabled.

Hi Folks,

Below calculated field is not allowing me to perform sum .Can you let me know it it needed to be converted to a number using regex to get the sum and running total ?

Any inputs would be really helpful Thank you . Workbook attached.

• ###### 1. Re: Need to perform SUM on this calculated Field . Sum is disabled.

It is likely already an aggregate - you may need something like a WINDOW_SUM or a FIXED LOD expression to return the sum of an aggregate -- here is one post with some options: Total() vs Window_Sum()

Also look into FIXED LOD expressions: FIXED Level of Detail Expressions

Cheers!

• ###### 2. Re: Need to perform SUM on this calculated Field . Sum is disabled.

See how it's colored blue? That means you have it as a discrete number.

Right-click just like you're doing now and select 'Convert to Continuous'. Then you should be good to go.

• ###### 3. Re: Need to perform SUM on this calculated Field . Sum is disabled.

I did try that . Below is the formula that i have used to derive . i need a running total on top of that ,but i can sum it either .

Final Projections =

--------------------------------------------------------

IF [Aver] = 0 and SUM([Actual]) = 0 THEN

PREVIOUS_VALUE(0)+([Average])

ELSEIF SUM([Actual]) =0 and [Aver] <>0 THEN

[Aver]

ELSE

SUM([Actual])

END

• ###### 4. Re: Need to perform SUM on this calculated Field . Sum is disabled.

Alright, first you have to convert it to continuous.

Once you do that, you can't perform an aggregation like sum or average on it because you already did so in the calculation itself. Instead of ending the calculation with SUM([Actual]), just end it with [Actual]. Now, your calculation ends with two possible options:

[Aver] or [Actual]

Now if [Aver] is already a calculated field that's been aggregated, you still won't be able to aggregate it once again. For example, if [Aver] is the result of a calculated field where you're saying to average something, it's already been aggregated.