I'm trying to compare forecast data with sales data in a cube. Sometimes we have a forecast with no actual sale, sometimes we have a sale with no forecast. How do just show 0% if one or the other does not exist? This is my current code... Green lines are ok because it is showing 0% where one of the amounts doesn't exist. I want it to do the same for the red circled ones.
CREATE MEMBER CURRENTCUBE.[Measures].[Forecast Accuracy Amount %]
AS IIF([Measures].[Sales Line Amount TCUSD SF] <> 0 OR [Measures].[F Amount] <> 0, 1-ABS(DIVIDE(([Measures].[F Amount] - [Measures].[Sales Line Amount TCUSD SF]) , [Measures].[F Amount])), NULL),
FORMAT_STRING = "Percent",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Sales Forecast' ;
EDIT - if both measure values don't exist they would both be null and wouldn't display. So I wouldn't want the percentage to display either and I only want to do the calculation if one of the quantities is not 0. Forecast accuracy should not show 100% if f amount is 0 and there is a sale as shown in the last img... this is the last thing to correct...
I prefer the safety of ISEMPTY
:
CREATE MEMBER CURRENTCUBE.[Measures].[Forecast Accuracy Amount %]
AS
IIF(
NOT ISEMPTY([Measures].[Sales Line Amount TCUSD SF])
OR
NOT ISEMPTY([Measures].[F Amount])
, 1-ABS(DIVIDE(
[Measures].[F Amount] - [Measures].[Sales Line Amount TCUSD SF]
,[Measures].[F Amount]
)
)
,NULL
),
FORMAT_STRING = "Percent",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Sales Forecast' ;
Slightly more complicated:
CREATE MEMBER CURRENTCUBE.[Measures].[Forecast Accuracy Amount %]
AS
IIF(
ISEMPTY([Measures].[Sales Line Amount TCUSD SF])
AND
ISEMPTY([Measures].[F Amount])
, NULL //<<if both are empty
, IIF(
NOT ISEMPTY([Measures].[Sales Line Amount TCUSD SF])
OR
NOT ISEMPTY([Measures].[F Amount])
,1-ABS(
DIVIDE(
[Measures].[F Amount] - [Measures].[Sales Line Amount TCUSD SF]
,[Measures].[F Amount]
)
)
,0
)
),
FORMAT_STRING = "Percent",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Sales Forecast' ;