I am having trouble limiting a sum function to only evaluate the data in the slicer. I need a sum of data calculated per employee and when there is a slicer of one employee my measure still evaluates all employee. For example:

WITH
MEMBER [Measures].[TotalEmpHours] AS
SUM([Employee].[Employee].members, [Measures].[Actual Ovt Hours])
select
{
[Measures].[TotalEmpHours],
[Measures].[Actual Ovt Hours]
}
on columns,
non empty
(
[Organization Level].[Level6].[Level6].members
) on rows
from
(
select
{[Employee].[Employee].&[204] }
on columns
from [BusinessAnalytics]
)

The above reports the overtime for one employee in [Measures].[Actual Ovt Hours], I expected [Measures].[TotalEmpHours] to be the same as there is only one employee in the slicer select. The real measure is the minimum of two values for each employee. This is a simplified example but I think it should work.

Is there another construct to iterate the employee set?

Any explanation and help would be great. I have no control over the query as it is from a pivot grid. If the filter (employee) is leftmost in the row area, then it works because the employee ends up in the row part of the query.

Thanks in advance, it been awhile

Doug

This is the expected behaviour for the calculation you have defined. Calculated expressions need to have the ability to "reach outside" any of the filters defined in the query. This behaviour is what enables scenarios like calculating YTD aggregations or "variation to same time last year" or "percent of total" style calculations. 

What happens is that effectively calculations start off calculating each cell by initially taking the context from the query (so the values on the visible axis and the slicers) and then anything you've set explicitly in the calculation overwrites the values coming from the context. So in your case it is always summing over all the employee members.

You can override this behaviour by using the EXISTING keyword. This keyword tells the MDX query engine to apply the query context to the following set. So changing your calculation to the following should resolve your issue and result in TotalEmpHours returning just the hours for the one employee in your example query.

WITH
MEMBER [Measures].[TotalEmpHours] AS
SUM( EXISTING [Employee].[Employee].members, [Measures].[Actual Ovt Hours])