# Average in MDX

### Question

• Hello, I need to do the following

IIF([if category level or above], [average of "direct" children],
IIF([if category below], [returns a measure], NULL)

)

Im summing teh children and divide by a count.

I tried using this or using the is level function but the average is not working. If I mannually do the average of the children it not matches the one that the calculation returns. I am doing something wrong?

Can anyone help me?

Thanks.

Sunday, May 19, 2013 8:50 PM

• Hi abernardo,

Here is an example about use AVG() function in the MDX query:
WITH MEMBER Measures.[Avg Gross Profit Margin] AS
Avg(
Descendants(
[Ship Date].[Fiscal].CurrentMember,
[Ship Date].[Fiscal].[Date]
),
Measures.[Gross Profit Margin]
)
SELECT
Measures.[Avg Gross Profit Margin] ON COLUMNS,
[Ship Date].[Fiscal].[Fiscal Year].[FY 2003].Children ON ROWS
FROM

Based on your description, it seems you want to get the average by using DAX. For example, the following formula returns the average of the values in the column, ExtendedSalesAmount, in the table, InternetSales:
=AVERAGE(InternetSales[ExtendedSalesAmount])

Best Regards,

Elvis Long
TechNet Community Support

Tuesday, May 21, 2013 5:40 AM

### All replies

• Can you post the exact query which you are using.

Regards,

Bharath

Monday, May 20, 2013 5:14 AM
• Hi abernardo,

Here is an example about use AVG() function in the MDX query:
WITH MEMBER Measures.[Avg Gross Profit Margin] AS
Avg(
Descendants(
[Ship Date].[Fiscal].CurrentMember,
[Ship Date].[Fiscal].[Date]
),
Measures.[Gross Profit Margin]
)
SELECT
Measures.[Avg Gross Profit Margin] ON COLUMNS,
[Ship Date].[Fiscal].[Fiscal Year].[FY 2003].Children ON ROWS
FROM

Based on your description, it seems you want to get the average by using DAX. For example, the following formula returns the average of the values in the column, ExtendedSalesAmount, in the table, InternetSales:
=AVERAGE(InternetSales[ExtendedSalesAmount])

Best Regards,

Elvis Long
TechNet Community Support

Tuesday, May 21, 2013 5:40 AM