Hi
We have a cube that has a calculated measure: 'Cost Contribution' defined. Cost contribution is basically revenues from each employee divided by the cost of his/her group. We can track revenues for every employee whereas costs can only be allocated to managers.
In a simple example we have the following employee structure
- Company
+ Jacobs
+ Miller
+ Gonzales
+ Stevens
Cost contribution for Stevens expresses Stevens' revenues compared to the costs of Miller and the hard-coded MDX formula is this:
CREATE MEMBER CURRENTCUBE.[Measures].[Cost Contribution]
AS (IIF(ISANCESTOR([Employee].[Employee].[Jacobs], [Employee].[Employee].CURRENTMEMBER) Or
[Employee].[Employee].[Jacobs] = [Employee].[Employee].CURRENTMEMBER,
[Measures].[Revenue] / ([Employee].[Employee].[Jacobs], [Measures].[Cost]),
[Measures].[Revenue] / ([Employee].[Employee].[Miller], [Measures].[Cost]))
),
FORMAT_STRING = "Percent",
NON_EMPTY_BEHAVIOR = { [Measures].[Revenue]},
VISIBLE = 1 ;
If no filter has been applied to the employee hierarchy the calculated measure works as expected. However if we filter for example for only Stevens, PivotGrid shows infinity instead of the correct value.
In Excel 2007 this works fine so we assume that the problem is not in our calculated member definition.
We believe this problem origins from your filter design. Somehow filtering for members can have an impact on the result of calculated measures (that might reference other member's measures in their formula).
Steps to Reproduce:
- Restore Analysis Services backup
- Drag the 'Employee Hierarchy' to the rows area.
- Drag the 'Cost Contribution' measure to the data area
- Expand the employee hierarchy to the lowest level (employee level 2)
-> Cost contribution for Stevens is 0.4 (6500 revenues against 16450 costs in Miller's unit). - Filter only for Stevens
-> Cost contribution for Stevens gets infinity and the value changes simply because we filtered the employee.
Actual Results:
Stevens' cost contribution gets infinity when filtering for member Stevens. This is wrong since it is 0.4.
Expected Results:
Filtering for members should not change the results of calculated members.
Stevens' cost contribution should still be 0.4 also when filtered for Stevens only.
Here are the screenshots showing the problem.