AVG Function :-
The AVG Function is used to calculate the Average of Non Empty Members present in the specified SET.
Please note that the MDX AVG Function will ignore the NULL Values.
--AVG Function
is used to calculate the average of non empty members present in the specified
Set.Please note
that The MDX Average Function will ignore the NULL values.
-- Actuall MDX
Query
SELECT {
[Measures].[Sales Amount]
}
ON COLUMNS,
{
[Dim Customer].[English Country Region Name].[English
Country Region Name]
} ON ROWS
FROM [MSBI]
--Example-1:
[Adding new Calculated Measure]
WITH MEMBER
[MEASURES].[Average]
AS
AVG (
[Dim Customer].[English Country Region
Name].[English Country Region Name]
,[Measures].[Sales
Amount]
)
SELECT {
[Measures].[Sales Amount]
,[MEASURES].[Average]
}
ON COLUMNS,
{
[Dim Customer].[English Country Region Name].[English
Country Region Name]
} ON ROWS
FROM [MSBI]
--Example-2
[Adding as a Dimension Hierarchy]
WITH MEMBER
[Dim Customer].[Geography
Hierarchy].[Average]
AS
AVG (
[Dim Customer].[English Country Region
Name].[English Country Region Name]
,[Measures].[Sales
Amount]
)
SELECT {
[Measures].[Sales Amount]
--,[Dim Customer].[Geography
Hierarchy].[Average]
}
ON COLUMNS,
{
[Dim Customer].[Geography Hierarchy].[English Country
Region Name]
,[Dim
Customer].[Geography Hierarchy].[Average]
} ON ROWS
FROM [MSBI]
--Example-3 (With Non Empty)
WITH MEMBER
[Dim Customer].[Geography
Hierarchy].[Average]
AS
AVG (
[Dim Customer].[English Country Region
Name].[English Country Region Name]
,[Measures].[Sales
Amount]
)
SELECT {
[Measures].[Sales Amount]
--,[Dim Customer].[Geography
Hierarchy].[Average]
}
ON COLUMNS,
NON EMPTY {
[Dim Customer].[Geography Hierarchy].[English
Country Region Name]
,[Dim
Customer].[Geography Hierarchy].[Average]
} ON ROWS
FROM [MSBI]
Thanks for Looking into this.....
No comments:
Post a Comment