Post reply

Function Avg() Question

  • March 23, 2005 at 8:31 am

    #109641

    I am trying to create an OLAP Cube measuring Tickets, Time, AvgDuration and other Average measures for analysis in Excel PivotTable Services WITH DRILLDOWN.  I need to create a measure on Average Duration.  Some of these Duration values are null.  If I use the traditional Sum(Duration) / Count(Tickets), the result is too high because it includes null values.  The Avg function excludes null values.  Can anyone demonstrate how to create a measure using the Avg() function or even the VBA Excell AVERAGE() funtion excluding nulls?  I would like to be able to create a DERIVED MEASURE if possible.

    Thanks

  • March 24, 2005 at 2:26 am

    #548677

    sum(filter([time].currentmember,(([measures].[Duration])>0)))

    /count(filter([time].currentmember,(([measures].[Duration])>0)))

     

    I hope this will help you

  • March 24, 2005 at 9:30 am

    #548795

    Thanks for replying.

    I tried to create a Calculated Measure on sum(filter([CreatedDateTime].currentmember,(([Measures].[DurationSum])>0)))/count(filter([CreatedDateTime].currentmember,(([Measures].[DurationSum])>0))) and AM gave syntax error - token not valid :

    sum(filter([CreatedDateTime]V.Vcurrentmember,(([Measures].[DurationSum])>0)))/count(filter([CreatedDateTime].currentmember,(([Measures].[DurationSum])>0)))

    So I tried

    sum(filter([CreatedDateTime].allmembers,(([Measures].[DurationSum])>0)))/count(filter([CreatedDateTime].allmembers,(([Measures].[DurationSum])>0))) , which the Value Expression checker accepted.

    When I reviewed the data in AM, the above expression produced #ERR.

     

  • March 28, 2005 at 2:42 am

    #549083

    Hi,

    You have to specify the level of the time dimension, Find below the example.

    If the lowest level is week.If Lowest level is day the change it to Day.

    iif([time].currentmember.level.name<>"Week",sum(filter({[time].currentmember.children},([Measures].[Durations]>0)))/count(filter({[time].currentmember.children},(([Measures].[Durations])>0))),[Measures].[Durations])

    you can also take all levels individually using multiple IIf statements.

    Hope this will help you.

    Regards,

    Tejal

     

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply