Rounding problem in Calculated Member of crosstab

[cwarner]

I have a crosstab with calculated members that creates an average. The problem is it always rounds down. So, if the average is 3.8, it will display as "3". I would prefer it to round to 1 decimal.

However, even when changing the formatting of the field to round to 1 decimal place, it just adds zeros (3.0).

I assume it may be related to my average formula that uses a crosstab value which is a "count".

Any advice how I could change this formula, to fix this rounding issue? Thanks!

local numbervar i; local numbervar avg; local numbervar cnt;

for i := 1 to CurrentColumnIndex-1 do ( avg := avg + GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex); cnt := cnt + 1; ); (avg+GridValueAt(CurrentRowIndex, 0, CurrentSummaryIndex))/(cnt+1);

[abhilash_kumar · Accepted solution]

Hi Cheryl,

You'd have to use a "display string" formula.

1. Right-click the header cell for the "Avg" column > Calculated Member > Edit ColumnValue formula.

The commented text at the top would indicate the datatype needed for this column. If it says datetime then use the formula below:

datetime(1890,01,01,00,00,00)

If it says date then use:

date(1890,01,01)

If it says text then use:

"Avg"

2. Right-click one of summary values in the calculated column > calculated member > edit calculation formula > delete the code and type in 0

3. Right-click one of the summary cells > format field > common tab > click the formula button beside "display string" and use this code:

IF year(GridRowColumnValue("field used as crosstabs column", CurrentColumnIndex)) = 1890 then
(
local numbervar i; 
local numbervar avg; 
local numbervar cnt;
for i := 0 to CurrentColumnIndex-1 do 
( 
avg := avg + tonumber(GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex)); 
cnt := cnt + 1; 
); 
totext(avg/cnt,1)
)
Else
totext(CurrentFieldValue,'#')

Note: Replace "field used as crosstabs column" with the correct database field. Also make sure that it is enclosed in double quotes and not curly braces.

-Abhilash

[cwarner · Accepted solution]

Works perfectly!

Thanks again, Abhilash!