locked
SSAS Average over Time for Distinct Counts RRS feed

  • Question

  • Hi All,

       I need to create a calculated member in the cube which does the function of average over time for distinct count. as distinct count is a non additive measure i am not getting the correct results. what I am trying to achieve is 

    sum(distinct count Members) / no of days 

    distinct counts
    6/1/2013 25
    6/2/2013 25
    6/3/2013 30
    total 26.6

    the calculated memeber should add the distinct counts and divide it by no of days so (25+25+30)/3 = 26.66

    the denominator should be dynamic so whatever range we are looking in that shoudl be in the denominator. please give me any suggestions

     

    Monday, August 5, 2013 4:51 PM

Answers

  • Hi

    Try this for your denominator to get the count of dates and replace with your dimensions and Facts

    WITH Member [Measures].[DatesCount] AS
    Count( 
          
          nonempty( EXISTING [Date].[Date].[Date].Members, [Measures].[DistinctCount]
                   )
               
         )
    let me know your results

    Prav

    • Marked as answer by Elvis Long Wednesday, August 14, 2013 12:11 PM
    Tuesday, August 6, 2013 1:30 AM