Calculating cost with different cost per group

Hello,

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.

Thanks!

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.

Daina / support@eazybi.com

Thanks, this works for us perfectly! :slight_smile: