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.
I'll see if I can get the sensitive data out and post it.
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.
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.
Adj DAP Capacity Average