How do I check for division by zero and null to avoid -1.#INF in cells
How do I check for division by zero and null to avoid -1.#INF in cells.
WITH
MEMBER [Measures].[ZeroValuePerUnit] AS 0
MEMBER [Measures].[DivideByZero] As [Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit] ,Format_String = '#.#0'
MEMBER [Measures].[CheckDivByZero] AS IIF([Measures].[ZeroValuePerUnit] = 0, Null, [Measures].[Internet Sales Amount]/[Measures].[ZeroValuePerUnit]),Format_String = '#.#0'
SELECT {[Measures].[Internet Sales Amount], [Measures].[ZeroValuePerUnit], [Measures].[DivideByZero], [Measures].[CheckDivByZero]} ON COLUMNS,
[Customer].[Country].Children ON ROWS
FROM [Adventure Works]
WHERE([Date].[Calendar].[Calendar Year].&[2004]);
Result will be:
Internet Sales Amount ZeroValuePerUnit DivideByZero CheckDivByZero Australia $2,563,884.29 0 1.#INF (null) Canada $673,628.21 0 1.#INF (null) France $922,179.04 0 1.#INF (null) Germany $1,076,890.77 0 1.#INF (null) United Kingdom $1,210,286.27 0 1.#INF (null) United States $3,324,031.16 0 1.#INF (null)