Returns: A number.
In this example, you want to evaluate years that had below-average profit. The following query uses the Abs function to return the profit amount and the amount below the average profit for below-average years.
WITH
MEMBER [Price Analysis].[Measures].[Average Profit Amount]
AS Avg( [Price Analysis].[Time].CurrentMember.Siblings,
[Price Analysis].[Measures].[Profit Amount] )
MEMBER [Price Analysis].[Measures].[Amount Below Average]
AS Abs( ([Price Analysis].[Time].CurrentMember,
[Price Analysis].[Measures].[Profit Amount])
- ([Price Analysis].[Time].CurrentMember,
[Price Analysis].[Measures].[Average Profit Amount]) )
SELECT { [Price Analysis].[Measures].[Profit Amount],
[Price Analysis].[Measures].[Amount Below Average] }
ON AXIS(0),
{ Filter( [Price Analysis].[Time].[All Time (Calendar)].Children,
([Price Analysis].[Time].CurrentMember,
[Price Analysis].[Measures].[Profit Amount])
< ([Price Analysis].[Time].CurrentMember,
[Price Analysis].[Measures].[Average Profit Amount]) ) }
ON AXIS(1)
FROM [Price Analysis]
Time | Profit amount | Amount below average |
---|---|---|
2002 | 263524.08 | 10525.747 |
2003 | 255214.96 | 18834.867 |