How can I cumulate values of a calculated meassure including a formular?

Questions & Answers
1

I’m not able to cumulate values for a chart. I would like to have them at the project level.
The values are calculated using a custom field with the following formula:

CASE
WHEN [Measures].[Issue Pre-Sizing (PT)] = ‘S (bis 5 PT)’ THEN 2.5
WHEN [Measures].[Issue Pre-Sizing (PT)] = ‘M (5 bis 15 PT)’ THEN 10
WHEN [Measures].[Issue Pre-Sizing (PT)] = ‘L (15 bis 50 PT)’ THEN 32.5
WHEN [Measures].[Issue Pre-Sizing (PT)] = ‘XL (50 bis 100 PT)’ THEN 75
WHEN [Measures].[Issue Pre-Sizing (PT)] = ‘XXL (100 bis 200 PT)’ THEN 150
END

Can anyone help me with this problem?

Thank you very much in advance.

dokpat

2

Hi @dokpat,
You need to use Sum() and DescendantsSet() functions to sum the values from issues per project level:

Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].CurrentHierarchy.Levels("Issue")),
    Not IsEmpty([Measures].[Issue Pre-Sizing (PT)])
    AND
    [Measures].[Issue Pre-Sizing (PT)] <> "(none)"
    AND
    [Measures].[Issues created]>0
  ),
  CASE
WHEN [Measures].[Issue Pre-Sizing (PT)] = "S (bis 5 PT)" THEN 2.5
WHEN [Measures].[Issue Pre-Sizing (PT)] = "M (5 bis 15 PT)" THEN 10
WHEN [Measures].[Issue Pre-Sizing (PT)] = "L (15 bis 50 PT)" THEN 32.5
WHEN [Measures].[Issue Pre-Sizing (PT)] = "XL (50 bis 100 PT)" THEN 75
WHEN [Measures].[Issue Pre-Sizing (PT)] = "XXL (100 bis 200 PT)" THEN 150
END
)

best,
Gerda

3

Hi Gerda,

thanks a lot!

This works perfectly. :slight_smile:
It is so helpful.

Best regards
Patrizio

1 Like