Absolute value for Sprint Commitment change

Questions & Answers
1

Hello,

I have the following drilling result for Sprint Commitment change Measure for one sprint:

image
image2996×654 55.3 KB

I’d like to have a formula, which returns me for the sprint ‘absolute’ value of changes. So, the total would be not ‘-4’, but ‘16’ in this case (5+5+5+1).

What the formula or way you can suggest?

Thanks in advance.

2

Hi @Anton_Kruglikov,

You can try to use the ABS() function to change negative values. The formula could look similar to the one below:

Abs([Measures].[Sprint commitment changes])

Best,
Roberts // support@eazybi.com

3

@roberts.cacus Sorry, it will not help me. It will return ‘4’ instead of ‘-4’ in example below. I need to have ‘16’.

4

@Anton_Kruglikov, you are right. In that case try creating a new calculated measure that iterates through all issues having values for this measure and summing up their absolute values. See the code below:

Sum(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    Abs([Measures].[Sprint Commitment Changes]) > 0
  ),Abs([Measures].[Sprint Commitment Changes])
)

To improve the performance of this calculated measure, I recommend adding additional conditions to the Filter() function.

Best,
Roberts // support@eazybi.com

5

Thanks @roberts.cacus,

Sorry, but didn’t get ‘adding additional conditions to the Filter() function’.
What do you suggest?

6

Hi @Anton_Kruglikov,

The calculated measure sums up the values from a subset of issues. The subset of issues is retrieved from the Filter() function, where all the issues are filtered by particular conditions. In the current case, the only condition is the absolute value of the measure “Sprint Commitment Changes” for the issues be greater than zero.

Depending on the report you are building, you can add additional conditions. It is preferable to have at least one condition of a real measure to tie it to the report context, and one or several based on properties, improving the performance of the Filter() function.

You already have one based on a measure. You can add one based on a issue property, for example, “Issue type”. If you would want to retrieve only issues with the type “Story”, you could add that to the formula:

Sum(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    [Measures].[Issue type] = 'Story'
    AND
    Abs([Measures].[Sprint Commitment Changes]) > 0
  ),Abs([Measures].[Sprint Commitment Changes])
)

The eazyBI documentation has more information about the Filter() function - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/filter

Best,
Roberts // support@eazybi.com