How to calculate average based on distinct counts in MDX

Question

My SSAS cube has the following fact and dimensions with the columns as shown below

FactActivity

DateKey, UserKey, ActivityKey, ActivityCount

DimDate

DateKey, Date, Week, Year

DimUser

UserKey, UserName, Gender

DimActivity

ActivityKey, ActivityName

I have created the distinct count measures of users and dates as follows

[Distinct Users]

COUNT(NONEMPTY([DimUser].[UserKey].[UserKey].Members, [Measures].[ActivityCount])

[Distinct Dates]

COUNT(NONEMPTY([DimDate].[DateKey].[DateKey].Members, [Measures].[ActivityCount])

Both these measures are working correctly as expected when I slice/pivot by ActivityName.

Now I wanted to calculate average days per user, so I created the metric as follows

[Avg Days Per User]

AVG([DimUser].[UserKey].[UserKey].Members, [Measures].[Distinct Dates])

But this is giving me wrong results.! i also tried

DIVIDE([Measures].[Distinct Days], [Measures].[Distinct Users])

Still I get wrong results...what i'm doing wrong?

whytheq whytheq · Accepted Answer · 2014-11-19T18:55:28

Maybe just adding in EXISTING will help?

AVG(
  EXISTING [DimUser].[UserKey].[UserKey].Members, 
  [Measures].[Distinct Dates]
)

Although trying to recreate something similar in AdvWks I seem top get a valid return without EXISTING:

WITH 
  MEMBER [Measures].[Avg Count Per Reseller] AS 
    Avg
    ( [Reseller].[Reseller].[Reseller].MEMBERS
     ,[Measures].[Reseller Order Count]
    ), format_string = "0.0000" 
SELECT 
  {[Measures].[Reseller Order Count],[Measures].[Avg Count Per Reseller]} ON 0
 ,{[Promotion].[Promotion].[All Promotions].Children} ON 1
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2005];