4 Replies Latest reply on May 10, 2012 2:38 PM by jared.thatcher

# how to do a Total of a Calculated Field

I have this if Statement as a calculated field

if[All Search Unit Total] < 4 THEN 47

elseif[All Search Unit Total] < 7 THEN 46

elseif[All Search Unit Total] < 10 THEN 45

elseif[All Search Unit Total] < 13 THEN 44

elseif[All Search Unit Total] < 16 THEN 43

elseif[All Search Unit Total] < 18 THEN 42

elseif[All Search Unit Total] < 20 THEN 41

elseif[All Search Unit Total] < 22 THEN 40

elseif[All Search Unit Total] < 24 THEN 39

elseif[All Search Unit Total] < 26 THEN 38

elseif[All Search Unit Total] < 28 THEN 37

elseif[All Search Unit Total] < 30 THEN 36

else 35 end

It works just fine for everything except the Total.  This logic applies to the total making the Total 35.  What I want is the actual total as an average but as long as the total is being calculated by the If Else statement I can't

Any help would be greatly appreciated,

here is a small snipit of what it returns

 Measure Values 35 38 35 37 35 47 TOTAL 35
• ###### 1. Re: how to do a Total of a Calculated Field

Hi Jared,

It is important to note that grand totals will use whatever aggregation is on the measure (i.e. your if then calculation will be applied to the grand total as well). Would you be able to post your workbook (saved as twbx file)? Maybe I/or someone else can come up with a work around.

-Tracy

• ###### 2. Re: how to do a Total of a Calculated Field

I'll see if I can get the sensitive data out and post it.

• ###### 3. Re: how to do a Total of a Calculated Field

No doesn't look like I'll be able to post this one.

I can't be the only one who has wanted to use a Total or Average of a calculated Field.  I could even add it in my IF statement if needed.  Something like

if[All Search Unit Total] < 4 THEN 47

elseif[All Search Unit Total] < 7 THEN 46

elseif[All Search Unit Total] < 30 THEN 36

else ( CURRENT FIELD'S TOTAL) end

Except trying this gives me a circular reference Error.

• ###### 4. Re: how to do a Total of a Calculated Field

Ok I found a workaround, Ghetto, But still a work around.

So I have my first calculated field called [Adj DAP Capacity] which does the ifstatment above.

I created a Second Calculated field and assigned it to = WINDOW_AVG(ZN([Adj DAP Capacity])).  This takes my the average of the calculated field in the new row.  Total is still broken but it provides the average I needed

So it looks like \/ the 38.8 is the number I wanted in the total but this works for now.

36                                  38.8

42                                  38.8

45                                  38.8

43                                  38.8

47                                  38.8

TOTAL                           Total

35                                  35