Need help with some nested calculated measures please!

Questions & Answers
1

Hi - I am new to calculated measures and could use some help. What I am trying to do is get a % value returned for each of the projects I am responsible for. This % will be calculated from "The number of stories with an epic link / (The number of stories with an epic link + The number of stories without an epic link). I have the project set as the row.

What I have done so far, I have created 2 calculated measures for each value I need. So I have a “issuesWithEpics” and “IssuesWithoutEpics” these seem to be calculating correctly, here is one as an exampe and the other is identicle but without the IsEmpty condition.

NonZero(
  Count(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
       (IsEmpty([Measures].[Issue Epic Link]))
       AND [Measures].[Issue status] <> "Closed"
       AND [Measures].[Issue status] <> "Done"
       AND [Measures].[Issue status] <> "Resolved"
       AND [Measures].[Issue type] = "Story"       
    )
  )
)

And here is the calculated measures to find the percent:

CatchException(
    [Measures].[IssuesWithEpics] /
   SUM([Measures].[IssuesWithEpics],[Measures].[IssuesWithoutEpics])

)

However the results are wrong (the percent is off as if the SUM is not adding properly and I am getting “IssuesWithEpics / IssuesWithoutEpics” only.
Also the same number show across all projects as if it is totalling all the values for all selected projects instead of breaking them out by project, here is a screenshot:

image
image1210×541 38.6 KB

2

Fixed the % issue by switching SUM (… , …) to (… + … )

3

Hi @Rich.hudson
Your first calculated measure is missing the report context. We usually recommend using a numerical measure that combines report dimensions.

You could try this formula:

  Sum(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
       (IsEmpty([Measures].[Issue Epic Link]))
       AND [Measures].[Issue status] <> "Closed"
       AND [Measures].[Issue status] <> "Done"
       AND [Measures].[Issue status] <> "Resolved"
       AND [Measures].[Issue type] = "Story"       
    ),
    CASE WHEN
    [Measures].[Issues created]> 0
    THEN
    1
    END
  )

That would calculate results based on Issues created in the report context.

Martins / eazyBI

Martins

4

Formula is not valid:
Syntax error at line 10, column 5, token ‘CASE’

5

@Rich.hudson
I noticed a syntax mistake in the formula, so I updated it in my previous post already.

Martins / eazyBI

6

Perfect - worked like a charm!!!