SQL SSAS Calculated Member showing #NUM! in Cube?

Question

I have 3 calculated members defined in my SQL Server 2008 SSAS Cube..

CALCULATE;         
/* Calculate Sales Volume */
CREATE MEMBER CURRENTCUBE.[Measures].[Ship Volume]
 AS [Measures].[QTY SHIPPED] + [Measures].[QTY ADJUST], 
FORMAT_STRING = "Standard", 
NON_EMPTY_BEHAVIOR = { [QTY SHIPPED], [QTY ADJUST] }, 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales';       
CREATE MEMBER CURRENTCUBE.[Measures].[Avg Price USD]
 AS [Measures].[NET SALES] / [Measures].[SHIP VOLUME], 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  ;    
CREATE MEMBER CURRENTCUBE.[Measures].[Avg Price CAD]
 AS [Measures].[NET SALES CAD] / [Measures].[SHIP VOLUME], 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  ; 

The syntax works fine, however when I look at my cube through Excel some of the values have #NUM! as the value.. not sure why this is? Is this because of divide by zero issue? How would I work around this?

Answer 1

Maybe check - A/B = C if B not equal 0 ? :

CALCULATE;         
/* Calculate Sales Volume */
CREATE MEMBER CURRENTCUBE.[Measures].[Ship Volume]
 AS [Measures].[QTY SHIPPED] + [Measures].[QTY ADJUST], 
FORMAT_STRING = "Standard", 
NON_EMPTY_BEHAVIOR = { [QTY SHIPPED], [QTY ADJUST] }, 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales';       
CREATE MEMBER CURRENTCUBE.[Measures].[Avg Price USD]
 AS Case
    When IsEmpty([Measures].[SHIP VOLUME] )
    THEN 0
    ELSE [Measures].[NET SALES] / [Measures].[SHIP VOLUME] END, 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  ;    
CREATE MEMBER CURRENTCUBE.[Measures].[Avg Price CAD]
 AS Case
    When IsEmpty([Measures].[SHIP VOLUME] )
    THEN 0
    ELSE [Measures].[NET SALES CAD] / [Measures].[SHIP VOLUME] END, 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Calculated Members' ,  ASSOCIATED_MEASURE_GROUP = 'Sales'  ; 

Also you can try change IsEmpty([Measures].[SHIP VOLUME] )

to [Measures].[SHIP VOLUME]=0

EDIT

Try change this IsEmpty([Measures].[SHIP VOLUME] ) to

[Measures].[SHIP VOLUME] is null OR [Measures].[SHIP VOLUME]=0