We have a custom formula drillable column and want to enable grand total row, but the grand total row shows NaN or 0.

Format used here is advanced =EvalWiki($_)

Somehow I noticed that this column is considered as text and not numeric and hence summable or total row feature shows NULL values.

Does anyone know how to get grand total to show total?

Have you ensured all the values in the rows you wish to sum or total are actual numbers?  NaN is short for "not a number" so my first guess is there maybe 1 or more values in these columns that are not actually numeric.  Start there and let me know if that fixes the issue.

Entire column has only numbers in it and since this is a custom formula drill column and we are using EvalWiki formula to convert text to hyperlink.

Here's a working example - maybe compare your settings to the ones below?

Thanks Christopher for your help.

I get an error when I type in this formula?

ERROR Message:

Encountered " "[[" "[[ "" at line 1, column 24.

Was expecting:

    "]]" ...

You'll want to modify the Format/Advanced formula to include all of the EvalWiki-related parameters, and then remove them from the top formula.

In order to correctly display totals in the bottom table row, Apptio needs this top formula ("Enter formula" section) to just contain the numerical equation with no formatting or extra characters. All of the formatting should instead be placed in the Format/Advanced section.

Does this look correct? null value (have all 0s in those 2 columns)

If FY PLAN column and its total sum row are working, then a similar approach should work for FY Forecast and FY Prior Forecast columns and total sum rows as well.

Just for testing purposes, you could try temporarily forcing at least one non-zero value into the FY Forecast column to see if the null converts into a proper value. If so, then it seems okay to let the total row say null for the time being, because you'll eventually have non-zero values in the two rightmost columns.

Is it possible to show '0'  instead of 'null'??

Not sure what's causing your total row to display null instead of zero--you may want to experiment further with formatting or open an Apptio support ticket if the issue persists.

Seems like when I add dummy numbers, it still shows 'null'?

Any feedback?

Am using this formula in last 2 columns showing 'null'

Theres an 'If' statement within Evalwiki

Formula: =EvalWiki(If(Forecast Dataset="Forecast2","[[dialog:report:/@.TableTransform:Forecast1 Intra Consumption CPU Transform/!FILTER[Billing Group="""&{CPU Distribution Pass 2.CPU Distribution Pass 2 Master.IT Resource Sub-Tower Name}&"""]/!FILTER[Destination Product="""&{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Sub-Tower Name}&"""]/Forecast1 Intra Drill|"&Currency($_)&"]]","[[dialog:report:/@.TableTransform:Forecast2 Intra Consumption CPU Transform/!FILTER[Billing Group="""&{CPU Distribution Pass 2.CPU Distribution Pass 2 Master.IT Resource Sub-Tower Name}&"""]/!FILTER[Destination Product="""&{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Sub-Tower Name}&"""]/Forecast2 Intra Drill|"&Currency($_)&"]]"))

@Chris Davidson @MATTHEW HALLS

Can you include your full data path?  

@MATTHEW HALLS

bankofamerica.com:BAC SC/

Reports/

.DateGoesHere/

CostModels/

Default/

.View:tab:Service Costing/

.View:Billing Product Manager Dashboard/

CPU Inter-Consumption/

.WithDefaultMetric-Plan/

.DrillTo/

CPU Intra-Consumption/

.AssignmentRatios/

.AddMetric-Forecast/

.AddMetric-Prior Forecast/

!NEWCOLUMN[{Cost Transfer YN}=If({CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Sub-Tower Name}={CPU Inter-Consumption.CPU Inter-Consumption Master.IT Resource Sub-Tower Name},"No","Yes")]/

!GROUPBY[{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Tower Owner},{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Sub-Tower Name},{CPU Inter-Consumption.CPU Inter-Consumption Master.IT Resource Tower Owner},{CPU Inter-Consumption.CPU Inter-Consumption Master.IT Resource Sub-Tower Name},{Cost Transfer YN}]/

!NEWCOLUMN[{Forecast Name}="Forecast"][hide]/

!NEWCOLUMN[{Forecast Dataset}=Lookup(Forecast Name,Version Table,Model,Dataset)][hide]/

!NEWCOLUMN[{Plan}=Rollup Value][FORMAT=Rollup Value,canSum]/

!NEWCOLUMN[{Plan }=Annual(Plan)/1000][=EvalWiki("[[dialog:report:/@.TableTransform:Inter Consumption Strategies Plan All Products/!FILTER[BILLING PRODUCT="""&{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Sub-Tower Name}&"""]/!FILTER[Destination Product="""&{CPU Inter-Consumption.CPU Inter-Consumption Master.IT Resource Sub-Tower Name}&"""]/Plan Inter Drill|"&Currency($_)&"]]")][canSum,numeric]/

!NEWCOLUMN[{Prior Forecast}=Prior Forecast.Rollup Value][FORMAT=Prior Forecast.Rollup Value,canSum]/

!NEWCOLUMN[{Prior Forecast }=Annual(Prior Forecast)/1000][=EvalWiki(If(Forecast Dataset="Forecast2","[[dialog:report:/@.TableTransform:Inter Consumption Strategies Forecast1 All Products/!FILTER[Billing Group="""&{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Sub-Tower Name}&"""]/!FILTER[Destination Product="""&{CPU Inter-Consumption.CPU Inter-Consumption Master.IT Resource Sub-Tower Name}&"""]/Forecast1 Inter Drill|"&Currency($_)&"]]","[[dialog:report:/@.TableTransform:Inter Consumption Strategies Forecast2 All Products/!FILTER[Billing Group="""&{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Sub-Tower Name}&"""]/!FILTER[Destination Product="""&{CPU Inter-Consumption.CPU Inter-Consumption Master.IT Resource Sub-Tower Name}&"""]/Forecast2 Inter Drill|"&Currency($_)&"]]"))][canSum,numeric]/

!NEWCOLUMN[{Forecast}=Forecast.Rollup Value][FORMAT=Forecast.Rollup Value,canSum]/

!NEWCOLUMN[{Forecast }=Annual(Forecast)/1000][=EvalWiki(If(Forecast Dataset="Forecast1","[[dialog:report:/@.TableTransform:Inter Consumption Strategies Forecast1 All Products/!FILTER[Billing Group="""&{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Sub-Tower Name}&"""]/!FILTER[Destination Product="""&{CPU Inter-Consumption.CPU Inter-Consumption Master.IT Resource Sub-Tower Name}&"""]/Forecast1 Inter Drill|"&Currency($_)&"]]","[[dialog:report:/@.TableTransform:Inter Consumption Strategies Forecast2 All Products/!FILTER[Billing Group="""&{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Sub-Tower Name}&"""]/!FILTER[Destination Product="""&{CPU Inter-Consumption.CPU Inter-Consumption Master.IT Resource Sub-Tower Name}&"""]/Forecast2 Inter Drill|"&Currency($_)&"]]"))][canSum,numeric]/

!FILTER_ZERO[{Forecast Name},{Forecast Dataset},{Plan },{Prior Forecast },{Forecast }]/

!SORT[{Forecast Name}|desc]/

!LIMIT[0,10,add_total]/

!LIMIT_COLUMNS[{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Tower Owner},{CPU Intra-Consumption.CPU Intra-Consumption Master.IT Resource Sub-Tower Name},{CPU Inter-Consumption.CPU Inter-Consumption Master.IT Resource Tower Owner},{CPU Inter-Consumption.CPU Inter-Consumption Master.IT Resource Sub-Tower Name},{Cost Transfer YN},{Plan },{Prior Forecast },{Forecast }][][][orderByIncludeList]/

Any luck?