For one day (several datetime records possible) I want to create a 2nd measure computing an AVG only for nonempty values based on my 1st measure. I've tried the following :
"CREATE MEMBER CURRENTCUBE.[Measures].[M2]
AS AVG([Measures].[M1]),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'DATA' ;"
But the SSAS AVG function seems to return me a Sum:
DateTime "M1"
1/1/16 12:10 "10"
1/1/16 13:10 "12"
1/1/16 14:10
1/1/16 15:10 "9"
1/1/16 16:10
1/1/16 17:10 "21"
1/1/16 18:10
1/1/16 19:10 "2"
Average for nonempty Measure values (10+12+9+21+2)/5 nonempty Values = 10,8
If you want an average daily value then @whytheq showed a way to do it in MDX. But I believe you want a simple average. The proper way to do that in SSAS is to create a measure M1 tied to SQL column M1 with AggregateFunction=Sum (which you already have) and a second measure M1_Count on SQL column M1 in that measure group with an AggregateFunction=Count.
Then create a calculated measure:
CREATE MEMBER CURRENTCUBE.[Measures].[M1 Avg] as
IIF([Measures].[M1_Count]=0,null,[Measures].[M1]/[Measures].[M1_Count]);