locked
Calculate average using MDX query RRS feed

  • Question

  • Hi,

    Suppose my fact table design is like

    Student | Subject | Marks

    Stud1    | Sub1    | 70

    Stud1    | Sub2    | 80

    Stud1   | Sub3     | NULL

    Stud1   | Sub4     | 70

    Stud2    | Sub1    | NULL

    Stud2    | Sub2    | 80

    Stud2   | Sub3     | 55

    Stud2   | Sub4     | 60

    Stud3    | Sub1    | 90

    Stud3    | Sub2    | 80

    Stud3   | Sub3     | 70

    Stud3   | Sub4     | 60

     

    I want to calculate average of marks (ignore NULL value) for each student.

    I am new to MDX .

     

    Please provide any Solution.

    Thanks !


    Prashant Mhaske
    Friday, September 2, 2011 10:38 AM

Answers

  • AVG() functions expects a set in the first argument whereas by using CurrentMember we pass a set with only one member which results in a sum (1/1=1).

    Use CurrentMember within Descendants OR as CurrentMember.Children.

    Just to simplify: average of country = sum of all players / count of players

    WITH MEMBER Measures.[Avg Score] AS
       Avg(
          [Mst Player].[Player].CurrentMember.Children
          , 
          [Measures].[Score]
       )
    SELECT
       Measures.[Avg Score] ON COLUMNS,
          [Mst Player].[Country].Children ON ROWS
    FROM
       [SSIS Test]
    

    Hope it helps.


    http://dailyitsolutions.blogspot.com/
    • Marked as answer by PrashantMhaske Monday, September 5, 2011 7:25 AM
    Friday, September 2, 2011 3:50 PM
  • Umair has a point, a point I missed in my first reply.<p>The AVG function expects a set, this set should probably be a period sliced by the CurrentMember of the Student dimension.<br/>Something in the lines of:</p>
    

    WITH MEMBER Measures.[Avg Score] AS
       Avg(
          <INSERT TIME LAPSE HERE>      , 
          ( [Mst Player].[Player].CurrentMember, [Measures].[Score] )
       )
    SELECT
       Measures.[Avg Score] ON COLUMNS,
          [Mst Player].[Country].Children ON ROWS
    FROM
       [SSIS Test]
    
    

     


    HTH - please mark correct answers
    • Marked as answer by PrashantMhaske Monday, September 5, 2011 7:25 AM
    Friday, September 2, 2011 4:00 PM

All replies

  • Hi Prashant

    As you can see in this article: http://msdn.microsoft.com/en-us/library/ms146067.aspx the MDX function AVG (average) by default ignores NULL.
    Therefore it should be possible 'out of the box' to solve your calculation

    Here is an example of an average calculation with AdventureWorks data:

    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 2006].Children ON ROWS
    FROM
       [Adventure Works]
    

    note: This is snipped out of the article, but the article is not up to date. The [Ship Date].[Fiscal].[Fiscal Year] doesn't contain the year 2003, but begins in 2006 instead. At least on my installation (SQL2008R2)

    The pseudo MDX for your query would be something like this:

    WITH MEMBER Measures.[Avg Mark] AS
       Avg(
          [Stud].[Sub].CurrentMember, 
          , 
          Measures.[Mark]
       )
    SELECT
       Measures.[Avg Mark] ON COLUMNS,
          [Stud].[Sub].Children ON ROWS
    FROM
       [YourCube]
    


    HTH - please mark correct answers
    Friday, September 2, 2011 12:46 PM
  • Hi Jens,

    By using below below code I am getting only sum of score group by Country not getting  average of respective country.

    My Cube structure is like

    Fact Table - [Player Score]

    Measures - Score

    Dimension Table - [Mst Player]

    Column - Country,Player

     

    WITH MEMBER Measures.[Avg Score] AS
    Avg(
    [Mst Player].[Country].CurrentMember
    ,
    [Measures].[Score]
    )
    SELECT
    Measures.[Avg Score] ON COLUMNS,
    [Mst Player].[Country].Children ON ROWS
    FROM
    [SSIS Test]


    Prashant Mhaske
    Prashant Mhaske
    Friday, September 2, 2011 2:42 PM
  • AVG() functions expects a set in the first argument whereas by using CurrentMember we pass a set with only one member which results in a sum (1/1=1).

    Use CurrentMember within Descendants OR as CurrentMember.Children.

    Just to simplify: average of country = sum of all players / count of players

    WITH MEMBER Measures.[Avg Score] AS
       Avg(
          [Mst Player].[Player].CurrentMember.Children
          , 
          [Measures].[Score]
       )
    SELECT
       Measures.[Avg Score] ON COLUMNS,
          [Mst Player].[Country].Children ON ROWS
    FROM
       [SSIS Test]
    

    Hope it helps.


    http://dailyitsolutions.blogspot.com/
    • Marked as answer by PrashantMhaske Monday, September 5, 2011 7:25 AM
    Friday, September 2, 2011 3:50 PM
  • Umair has a point, a point I missed in my first reply.<p>The AVG function expects a set, this set should probably be a period sliced by the CurrentMember of the Student dimension.<br/>Something in the lines of:</p>
    

    WITH MEMBER Measures.[Avg Score] AS
       Avg(
          <INSERT TIME LAPSE HERE>      , 
          ( [Mst Player].[Player].CurrentMember, [Measures].[Score] )
       )
    SELECT
       Measures.[Avg Score] ON COLUMNS,
          [Mst Player].[Country].Children ON ROWS
    FROM
       [SSIS Test]
    
    

     


    HTH - please mark correct answers
    • Marked as answer by PrashantMhaske Monday, September 5, 2011 7:25 AM
    Friday, September 2, 2011 4:00 PM
  • Thanks Very much Umair !
    Prashant Mhaske
    Monday, September 5, 2011 7:25 AM
  • Thanks Very much Jens !
    Prashant Mhaske
    Monday, September 5, 2011 7:27 AM
  • Hi Friend,

    Could you please give me syntax of calculating average of each player according to country ?

     

    I tried with below syntax but i am getting null value  to each player.

    WITH MEMBER Measures.[Avg Score] AS
       Avg(
          [Mst Player].[Player].CurrentMember.Children
          --[Mst Player].[Country].CurrentMember.Children
          ,
          [Measures].[Score]
       )
    SELECT
       Measures.[Avg Score] ON COLUMNS,
          [Mst Player].[Player].CHILDREN ON ROWS
    FROM
       [SSIS Test]

     

    I want output in below format

    Country |  Player  |  Total Score | Average Score !

    Thanks in Advance !



    Prashant Mhaske
    Thursday, September 8, 2011 7:38 AM