locked
Average of Measure in SSAS RRS feed

  • Question

  • Hello All,

    I've an Employee  dimension with EmployeeIDs and Measures table with year, month, Department, EmployeeID etc where HeadCount as measure i.e.

     

    EmployeeID (Dim table)

    XYZ

     

    Fact Table

    Year       Month  Department       EmployeeID       HC

    2010       10       Department_1  XYZ                 1

    2010       11       Department_1  XYZ                 1

    2010       10       Department_5  XYZ                 1

     

    In my cube I need average of HC as each Employee head count is always one i.e number of employee(s) in year or month or dept should be always One

    I tried with calculated measure

    AVG(EmployeeID.CurrentMember, [Measures].[HC]), but this returns me the sum of employee HC i.e. 3

    As of my knowledge it is not averaging total employeeID’s (XYZ) head count in measures table, but considering each XYZ as ‘currentmember’ and averaging it. i.e.

    1/1+1/1+1/1 = 3 what I need is 3(add HC with ID XYZ)/3(count of XYZ in measures) =1

     

    Any help will be appreciated

     

    Thanks

    santosh

    Tuesday, September 13, 2011 8:42 AM

Answers

  • For this you will have to include the other dimensions set part of the Avg function.
    Your calculate member could be something like

    AVG(Month.Month.Members , (EmployeeID.CurrentMember,[Measures].[HC])) or 

    AVG({Department.Department.Members * Month.Month.Members} , (EmployeeID.CurrentMember,[Measures].[HC])).

     

    Similar query in Adventure works would be

    WITH MEMBER [Measures].[TheAVG] AS AVG([Date].[Calendar].[Calendar Year], 
     ([Customer].[Customer Geography].CurrentMember,[Measures].[Internet Order Quantity])),FORMAT_STRING = "##.##"
    SELECT {[Measures].[Internet Order Quantity], [Measures].[TheAVG]} on 0,
    Nonempty([Customer].[Customer Geography].[Customer]* [Date].[Calendar].[Calendar Year], [Measures].[Internet Order Quantity]) on 1
    from [Adventure Works]
    

    hope this helps

     

    • Proposed as answer by Sorna Kumar Muthuraj Tuesday, September 13, 2011 2:05 PM
    • Marked as answer by Jerry Nee Monday, September 26, 2011 10:09 AM
    Tuesday, September 13, 2011 1:31 PM
    Answerer