none
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

Answers

  • 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
       [Adventure Works]

    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])

    Could you please elaborate your requirement with more detail? It will benefit for us to help you solve this issue.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, May 21, 2013 5:40 AM
    Moderator

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
       [Adventure Works]

    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])

    Could you please elaborate your requirement with more detail? It will benefit for us to help you solve this issue.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, May 21, 2013 5:40 AM
    Moderator