Multiple Components per Issue Calculation

AWPPMT · October 8, 2022, 11:04pm

Have issues with multiple components; note on the screen clip the erroneous issue count based on components-per-issue .

I would like to divide Issues created by the issue-component-count that would be reflected in a user-defined issue-created measure. As an example, an issue with two components would result in a .5 issue count for each component; .33 for issues for three components, etc. Would like to do the same for the Hours spent with sub-tasks measure as well.

Help please.

oskars.laganovskis · October 18, 2022, 5:12pm

Hi,

Multi-value fields raise some concerns for reporting. Components are even trickier as you might access them from the default dimension Project or import them into a separate dimension by name.
The expression needs to handle various scenarios:

one specific component selected on report rows or columns or Page filters - value should be divided by the number of applicable components
multiple and possibly not all components selected on report Pages, none on report rows or columns - value should be divided by the number of the applicable components and multiplied by the number of applied components
multiple projects selected on report Pages, but components not present on report rows or columns - the same as 2), but a different level on default dimension

The expression if using the Component level on the Project dimension might be as follows.

CASE WHEN
--multiple or all components selected on page filter
 [Project].Level.Name = "(All)"
  OR
 [Project].Level.Name = "Project"
THEN
--hours spent when ignoring components
  ([Project].DefaultMember,
   [Measures].[Hours spent with sub-tasks])
  /
--divide by the number of applicable components  
 Count(
   Filter(
    [Project].[Component].GetMembersByKeys(
       [Issue].CurrentHierarchyMember.Get('Component IDs')),
    ([Measures].[Hours spent with sub-tasks]) > 0))
 *
--multiply by the number of components selected
 Count(
  Filter(
   DescendantsSet([Project].CurrentHierarchyMember, [Project].[Component]),
     [Measures].[Hours spent with sub-tasks] > 0))
ELSE
--split values per each component
--take the total value regardless of components
 ([Project].DefaultMember,
  [Measures].[Hours spent with sub-tasks])
 /
--divide by the number of components applicable
 Count(
   Filter(
    [Project].[Component].GetMembersByKeys(
       [Issue].CurrentHierarchyMember.Get('Component IDs')),
   [Measures].[Hours spent with sub-tasks] > 0))
END

However, if using the Component dimension, the expression might look as follows.

CASE WHEN
--multiple or all components selected on page filter
[Component].Level.Name = "(All)"
THEN
--hours spent when ignoring components
  ([Component].DefaultMember,
   [Measures].[Hours spent with sub-tasks])
  /
--divide by the number of applicable components  
 Count(
   Filter(
    [Project].[Component].GetMembersByKeys(
       [Issue].CurrentHierarchyMember.Get('Component IDs')),
    ([Measures].[Hours spent with sub-tasks],
     [Component].DefaultMember) > 0)
   )
 *
--multiply by the number of components selected
 Count(
  Filter(
   DescendantsSet([Component].CurrentHierarchyMember, [Component].[Component]),
     [Measures].[Hours spent with sub-tasks] > 0))
ELSE
--split values per each component
--take the total value regardless of components
  ([Component].DefaultMember,
  [Measures].[Hours spent with sub-tasks])
 /
--divide by the number of components applicable
 Count(
   Filter(
    [Project].[Component].GetMembersByKeys(
       [Issue].CurrentHierarchyMember.Get('Component IDs')),
    ([Measures].[Hours spent with sub-tasks],
     [Component].DefaultMember) > 0))
END

You might replace the measure “Hours spent with sub-tasks” with “Issues created” for the number of issues.

Regards,
Oskars / support@eazyBI.com

AWPPMT · October 18, 2022, 6:26pm

Very much appreciated Oskars!

AWPPMT · October 23, 2022, 10:20pm

Note NaN on table totals in the attached image.

oskars.laganovskis · October 24, 2022, 8:20am

Hi @AWPPMT,

The Totals is yet another specific case. It is neither a specific issue nor a legal parent of displayed issues.

For the Totals row, the member is “$total_agregate”, which does not have properties. Therefore the result is zero divided by zero, which is “Not a Number”.

The solution is to wrap the calculated measure into a specific condition to either display the value or sum it up for the “Total”.

The expression for the “display” measure might be as follows.

CASE WHEN
[Issue].CurrentHierarchyMember.Name = "$total_aggregate"
THEN
Sum(
  VisibleRowsSet(),
  [Measures].[measure split by components]
)
ELSE
 [Measures].[measure split by components]
END

Regards,
Oskars / support@eazyBI.com

AWPPMT · October 24, 2022, 3:31pm

Works like a charm Oskars!

Suggest this code be incorporated into a near-term EazyBI release as this should considered a fix and not a feature/enhancement; current EazyBI release generates erroneous results in multi-component issue calculations (of which you have a resolution)

AWPPMT · October 24, 2022, 9:09pm

Encountered some additional problems in the our Prod environment (works in the Dev environment). Using the the following Hours spent with sub-tasks - split by components generates an Infinity as the column value, as depicted in the attached screen-clip.

CASE WHEN
--multiple or all components selected on page filter
 [Project].Level.Name = "(All)"
  OR
 [Project].Level.Name = "Project"
THEN
--hours spent when ignoring components
  ([Project].DefaultMember,
   [Measures].[Hours spent with sub-tasks])
  /
--divide by the number of applicable components  
 Count(
   Filter(
    [Project].[Component].GetMembersByKeys(
       [Issue].CurrentHierarchyMember.Get('Component IDs')),
    ([Measures].[Hours spent with sub-tasks]) > 0))
 *
--multiply by the number of components selected
 Count(
  Filter(
   DescendantsSet([Project].CurrentHierarchyMember, [Project].[Component]),
     [Measures].[Hours spent with sub-tasks] > 0))
ELSE
--split values per each component
--take the total value regardless of components
 ([Project].DefaultMember,
  [Measures].[Hours spent with sub-tasks])
 /
--divide by the number of components applicable
 Count(
   Filter(
    [Project].[Component].GetMembersByKeys(
       [Issue].CurrentHierarchyMember.Get('Component IDs')),
   [Measures].[Hours spent with sub-tasks] > 0))
END

Note also that this problem exists on wrapper measures (with the leading underscore) as well. Testing in the Prod environment indicates that problem seems to exist where there is no component defined (this doesn’t seem to be a problem in the Dev environment).

AWPPMT · October 25, 2022, 5:12pm

Solved the problem; need to test and manage divide-by-zero:

/* 
Do NOT use this measure directly.
This measure MUST be wrapped in another measure comprised of the
following code or a NaN will result in a column sum total.

CASE WHEN
[Issue].CurrentHierarchyMember.Name = "$total_aggregate"
THEN
Sum(
  VisibleRowsSet(),
  [Measures].[Hours spent with sub-tasks - split by components]
)
ELSE
 [Measures].[Hours spent with sub-tasks - split by components]
END
*/

CASE WHEN
--one or more issue components exist
  CACHE(
    Count(
      Filter(
        [Project].[Component].GetMembersByKeys(
        [Issue].CurrentHierarchyMember.Get('Component IDs')),
        [Measures].[Hours spent with sub-tasks] > 0
      )
    )
  ) > 0
THEN
  CASE WHEN
  --multiple or all components selected on page filter
    ([Project].Level.Name = "(All)"
    OR
    [Project].Level.Name = "Project")
      AND
      [Measures].[Hours spent with sub-tasks] > 0
  THEN
  --hours spent when ignoring components
    ([Project].DefaultMember,
    [Measures].[Hours spent with sub-tasks])
    /
--divide by the number of applicable components  
   Count(
     Filter(
        [Project].[Component].GetMembersByKeys(
        [Issue].CurrentHierarchyMember.Get('Component IDs')),
        ([Measures].[Hours spent with sub-tasks]) > 0))
   *
  --multiply by the number of components selected
   Count(
     Filter(
       DescendantsSet([Project].CurrentHierarchyMember, [Project].[Component]),
       [Measures].[Hours spent with sub-tasks] > 0
      )
    )
  ELSE
  --split values per each component
  --take the total value regardless of components
    ([Project].DefaultMember,
    [Measures].[Hours spent with sub-tasks])
    /
  --divide by the number of components applicable
    Count(
      Filter(
        [Project].[Component].GetMembersByKeys(
        [Issue].CurrentHierarchyMember.Get('Component IDs')),
        [Measures].[Hours spent with sub-tasks] > 0
      )
    )
  END
ELSE
--no issue components exist
  [Measures].[Hours spent with sub-tasks]
END