Formatting KPI goals for excel 2010 in BIDS

Question

I have a KPI (with value,goal & status) created using BIDS. When I view it using excel 2010 pivot table the kpi goal shows #value but it is used to calculate the status.

How do I hide the entire kpi goal or make it show the exact value instead of #value.

Below is the code I used for kpi goal.

CASE WHEN 
[Measure].[Measure - SubMeasure Desc].currentmember.level  is 
[Measure].[Measure - SubMeasure Desc].[Submeasure Desc]
 THEN
vba!format([Measures].[Threshold Min],[Measures].[Threshold Max],"percent")
ELSE NULL
END

The KPI status which is based on the goal and value is displaying correctly. The KPI value is okey too.....the problem is on the KPI goal only. Here is the complete list if it helps to clarify my question.

KPI VALUE: [Measures].[NCQA Rate]
KPI Goal:
CASE WHEN 
 [Measure].[Measure - SubMeasure Desc].currentmember.level is 
 [Measure].[Measure - SubMeasure Desc].[Submeasure Desc] 
THEN 
 vba!format([Measures].[Threshold Min],[Measures].[Threshold Max],"percent") 
ELSE 
 NULL 
END

KPI STATUS:
CASE WHEN 
 IsEmpty(KPIValue("NCQA COMMERCIAL")) 
THEN 
 NULL 
WHEN 
 KPIValue("NCQA COMMERCIAL") < 
   ([Benchmark].[Benchmark Type].&[NCQA National 2011 Commercial], 
    [Measure].[Measure - SubMeasure Desc].currentmember,
    [Measures].[Threshold Min]) /100 
then 
 -1 
WHEN 
 KPIValue("NCQA COMMERCIAL") > 
 ([Benchmark].[Benchmark Type].&[NCQA National 2011 Commercial], 
  [Measure].[Measure - SubMeasure Desc].currentmember,
  [Measures].[Threshold Max])/100 
then 
 1 
ELSE 
 0 
END

Answer 1

The KPI section is usually meant to return IF(Condition is TRUE 1, 0) and with those indicators you can formulate your KPI values. Keep it mind it does not have to be 1 or 0, you can do any decimal between 0 and 1 which I often use to represent percentages. With that in mind, you could simply display the value on a hidden Excel sheet and perform your calculation from there.