October 9, 2014 at 1:13 pm
Hello,
I am stumped on this, I am also new to MDX. After searching the web for a solution I could not figure this one out so I am posting here in hopes of finding a solution. Here goes.
I am working on a Human Resources cube. I have a Headcount Measure Group, Termination Measure Group, Employee Dimension and Department Dimension. There are more but these are the ones I'll use for this issue. I created a calculated measure called Turnover by doing [Measures].[Termination Count]/[Measures].[Emp Headcount] and it works perfect. Here's where the issue lies, In the employee dimension I have a field called Attrition, which is either desired or undesired. I need a couple calculated measures that can give me desired and undesired turnover percentages. For example below is what i have tried.
Desired Turnover Measure
Case
When [Employee].[Attrition].CurrentMember = [Employee].[Attrition].&[Desired] Then
[Measures].[Termination Count]/[Measures].[Emp Headcount]
else 0 end
Undesired Turnover Measure
Case
When [Employee].[Attrition].CurrentMember = [Employee].[Attrition].&[Undesired] Then
[Measures].[Termination Count]/[Measures].[Emp Headcount]
else 0 end
The percentages I am getting from these measures are not correct, I am getting a lot of zeros.
I have verified the underlying data in the Employee Dimension that everyone does have a value for attrition, either desired or undesired.
Any help would be greatly appreciated.
Below is a link to the output I am getting.
https://drive.google.com/file/d/0B_JQqi99mtEKSjdoSG8wZWZTYUk/view?usp=sharing
October 9, 2014 at 1:32 pm
I think you'll want to use SCOPE. Here's a very similar requirement with a reasonable example.
Steve.
October 9, 2014 at 2:40 pm
Thank you Thank you Thank you!
Worked like a charm.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply