We want to calculate development ticket costs. We have in Columns ‘Hours spent’ and then split with ‘Logged by Group’. We have several partners who have different cost. So i made calculated measure using Case function. Similar to this:
CASE
WHEN [Logged by Group].CurrentMember.Name = ‘Partner1’ THEN [Measures].[Hours spent] *30
WHEN [Logged by Group].CurrentMember.Name = ‘Partner2’ THEN [Measures].[Hours spent] *32
WHEN [Logged by Group].CurrentMember.Name = ‘Partner3’ THEN [Measures].[Hours spent] *38
WHEN [Logged by Group].CurrentMember.Name = ‘Partner4’ THEN [Measures].[Hours spent] *30
ELSE [Measures].[Hours spent] *25
END
Now i tried to Sum the results, by making calculated member under Logged by Group similar to this:
[Logged by Group].[Employee] + [Logged by Group].[Partner1] + [Logged by Group].[Partner2] + [Logged by Group].[Partner3] + [Logged by Group].[Partner4]
But it uses [Measures].[Hours spent] *25 in the adding calculation and the result comes out wrong.
Maybe there is a better way do this kind of calculations?
What is needed, that partner work cost is calculated with their price (which works with using case) and then Sum would be correct per ticket.
The formula uses a logged by group member name to detect what cost should be applied. You created a new member for total with a different name and the formula uses the ELSE cycle when comparing the member by this name it.
You would like to access individual members of the totaling calculated member to get a cost specified per group.
I would suggest using a different formula for a calculated member in Logged by Group. The current formula with a sum of members does not give access to the members. You would like to list the members within the Aggregate. The function Aggregate will make sure you can access individual members.
Aggregate ({
[Logged by Group].[Employee],
[Logged by Group].[Partner1],
[Logged by Group].[Partner2],
[Logged by Group].[Partner3],
[Logged by Group].[Partner4]
})
Then you would like to update the current formula Calculated average cost by accessing those members and summing results.
Sum(
DescendantsSet([Logged by Group].CurrentMember, [Logged by Group].[Group]),
CASE [Logged by Group].CurrentMember.Name
WHEN 'Partner1' THEN [Measures].[Hours spent] *30
WHEN 'Partner2' THEN [Measures].[Hours spent] *32
WHEN 'Partner3' THEN [Measures].[Hours spent] *38
WHEN 'Partner4' THEN [Measures].[Hours spent] *30
ELSE [Measures].[Hours spent] *25
END
)
)
I used a small update moving [Logged by Group].CurrentMember.Name after CASE. This allows me to simplify the WHEN part. It is applicable if you compare names.
The function DescendantsSet works with any selection, including individual members and calculated members.
Hi - I have exact the same question like mentioned here.
Different JIRA users logging time on tickets.
All users are in multiple groups.
I would like to calculate different costs according to the group a user is in.
Sum(
DescendantsSet([Measures].[Logged by groups].CurrentMember, [Measures].[Logged by Groups].[Group]),
CASE [Measures].[Logged by groups].CurrentMember.Name
WHEN “cultuurnet” THEN [Measures].[Hours spent] *300
WHEN “UID External” THEN [Measures].[Hours spent] *200
ELSE [Measures].[Hours spent] *100
END
)
)
This does not seem to work as all of these users have the cost 100 - should be 300 as they are in the group ‘cultuurnet’
