SSAS dealing with non linked data

Question

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...

0.00% is returned when `1-ABS(DIVIDE(([Measures].[F Amount] - [Measures].[Sales Line Amount TCUSD SF]) , [Measures].[F Amount]))` returns 0. A blank is returned when a NULL value is encountered. What is the problem with this? — SouravA, Jul 12 '16 at 10:46
I don't want blanks. If either of the amounts don't exist i want to show 0%. If i change the NULL to 0 it will fill all empty space with 0 and SSAS will show all lines regardless of whether there is a measure or not — jhowe, Jul 12 '16 at 10:59
ok so let me get this straight - you want a "0.00%" when either of the values don't exist? What if both the values do exists but the expression `1-ABS(DIVIDE(([Measures].[F Amount] - [Measures].[Sales Line Amount TCUSD SF]) , [Measures].[F Amount]))` returns 0?? In case both the values don't exist, what do you want? — SouravA, Jul 12 '16 at 12:03
Yes i want 0% if either or don't exist. If both exist and the calc returns 0 that's fine. If both the values don't exist show 0% — jhowe, Jul 12 '16 at 12:05
Then, can you go ahead with BeanFrog's suggestions and change the NULL to 0? — SouravA, Jul 12 '16 at 12:10
Sorry if both values don't exist they would both be null and wouldn't display. So I wouldn't want the percentage to display either... apologies... and i only want to do the calc if one of the quantities is not 0... — jhowe, Jul 12 '16 at 12:19

whytheq · Accepted Answer · 2016-07-12T13:41:28.280

3

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' ;

edited Jul 12 '16 at 13:41

answered Jul 12 '16 at 12:37

whytheq

34,466
65
172
267

Hi, i'm not sure you read the question properly, I want the calculation to work if the one of the measures is not 0 I also do not want to fill all the measures with 0's as it will bring in all items regardless of whether they are used or not. All i want to do is simply add a 0 for the rows where they are blank as highlighted in the pic – jhowe Jul 12 '16 at 13:09
when you say "All i want to do is simply add a 0 for the rows where they are blank" do you actually mean "All i want to do is simply add a 0 for the rows where they are blank or 0" ? – whytheq Jul 12 '16 at 13:16
hi i think the first example will do what i want but i can't test the second one there is an error before the second IIF and what's the difference between NOT ISEMPTY and NONEMPTY? i was getting different results... – jhowe Jul 12 '16 at 13:38
1

(@jhowe woops #1 - a missing comma for the nested IIFs) – whytheq Jul 12 '16 at 13:39
1

(@jhowe woops #2 + a missing bracket for the nested IIFs) – whytheq Jul 12 '16 at 13:41
this is close, however we are getting a 100% forecast accuracy for 0 forecast amount which is incorrect. It should be showing 0%, if we have a 0 forecast for a large sale it's not a very good percentage! ;) i've added another pic – jhowe Jul 12 '16 at 14:24
Also @whytheq will this also work depending on the dimensions that I select? if not how can I accommodate for that? – jhowe Jul 12 '16 at 15:15
this is so close! please help me finish this is urgent now... thanks... – jhowe Jul 12 '16 at 15:48

score 2 · Answer 2 · answered Jul 15 '16 at 17:25

I think whytheq's answer did most of it. The only thing I think you needed was to check if both values are more than zero to return a score, and yes, it will work with any dimension that your SSAS model allow. Something like the following should give you what you need:

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( 
          //Getting here means, at minimum, one of the measures is not empty
          // Calculate the score only if both values are non equal to zero otherwise default to zero
          [Measures].[F Amount] <> 0 AND
          [Measures].[Sales Line Amount TCUSD SF] <>0 
      ,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' ;

SSAS dealing with non linked data

2 Answers2