 Average of Measure in SSAS • 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

• 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