I’d like to create a calculated member formula that shows the hours spent for each project, but for a certain project called Internal, I want it to be zero. And for some important projects, I want hours spent to be multiplied by some factor. Here’s my current formula that’s not working as expected:
CASE
when [Project].Name = ‘Internal’ then [Measures].[Hours spent] * 0
when [Project].Name = ‘Important Project’ then [Measures].[Hours spent] * 2
ELSE [Measures].[Hours spent]
END
Also how would the formula change if I want a different factor for a specific project category instead of specific projects?
Thanks for posting your question!
You are very close to the solution, just a few adjustments are needed!
See screenshot below : I have selected Project dimension in Rows and I am at the Project level under the Category level.
I created 3 measures :
Project name = [Project].CurrentHierarchyMember.Name
Category Name = When having Project selected under the Category level, it makes Category being the Parent level for the Project, therefor, to retreive the Categogry name, you can use measure [Project].CurrentHierarchyMember.Parent.Name
And for your formula, you may use sample below. In one case statement I am checking for Project name, in the other one I am looking for the Category Name.
CASE
When [Project].CurrentHierarchyMember.Parent.Name = 'Internal' Then [Measures].[Hours spent] * 0
When [Project].CurrentHierarchyMember.Name = 'DEMO Alpha' Then [Measures].[Hours spent] * 2
ELSE [Measures].[Hours spent]
END
