Average only for NonEmpty - SSAS MDX
source link: https://www.codesd.com/item/average-only-for-nonempty-ssas-mdx.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Average only for NonEmpty - SSAS MDX
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]);
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK