Avg
Returns the average value of a numeric expression evaluated over a set.
Before you create the calculated measure with the average function, check if you can use standard functionality to add statistical calculations.
Syntax
Avg(Set_Expression, Numeric_Expression)
Arguments
Set_Expression | MDX expression that returns a set for which the average value will be calculated. |
---|---|
Numeric_Expression | MDX expression that returns a numeric value that should be used to calculate the average value. |
Returns
Number | The average value of Numeric_expression over a set |
---|
Examples
Example 1
The following example calculates the average time in days for unresolved issues from the last status change till today.
The formula pulls in a set of unresolved issues only and for this set will look for unresolved relevant to report selections and will calculate days for those issues from status updated date till now.
Avg( Filter( Descendants([Issue].CurrentMember, [Issue].[Issue]), -- for unresolved issues only IsEmpty([Measures].[Issue resolution date]) AND -- show on time for issue creation date DateInPeriod( [Measures].[Issue created date], [Time].CurrentHierarchyMember) ), -- calculate days in current status for each relevant issue CASE WHEN -- check if issue is relevant for the report [Measures].[Issues created] > 0 THEN -- calculated days in status DateDiffDays([Measures].[Issue status updated date], Now()) END ) )
See the example report Issues days in current status overview in our demo account. The calculated measure Average days in current status uses the formula above to calculate the average days in the status for any statuses used in the report.
Example 2
The following example finds the average amount of resolved issues in the last 52 weeks.
Avg( {[Time.Weekly].[Week].CurrentDateMember.Lag(52): [Time.Weekly].[Week].CurrentDateMember.PrevMember}, CoalesceEmpty( ([Measures].[Issues resolved],[Time].DefaultMember), 0 ) )
See the example report Predicted Completion date in our demo account. The calculated measure Rolling predicted date uses the formula above to calculate the predicted resolution date based on the velocity of 52 weeks.
See also
- Description of aggregate functions
- Function Descendants()
- Function Filter()
- Functions Aggregate()