CumulativeSum replacing value of certain rows

Questions & Answers
1

Hi, I have a table that shows epic and remaining estimates per epic and I have a row showing cumulative sum of the remaining estimates.

When the user opens an epic (clicks on the + in front) also the tasks under the epics (including remaining estimates are shown). Now the work on the open epic is counted twice (once on the epic and once on the task under the epic).

To solve this I would want to exclude the rows that show Tasks (rather than epics) from the cumulative sum of the visible rows.

I tried to do this by computing the sum in a custom measure that returns PreviousRowValue([Measures].[CumulativeSum]) if the issue type is ‘Task’ but this fails saying the measure ‘[Measures].[CumulativeSum]’ refers to itself. If I just return ‘0’ then it works (and tasks are excluded from the cumulative sum but I would really prefer to show the cumulative sum from the epic instead of 0 (or set the epic cumulative sum to 0 in case it is opened and then actually count the tasks, this would be even better).

Thx, Alex

2

I found a partial answer to my question here (how to exclude certain levels from the cumulative sum): Cumulative sum of calculated measure for tasks grouped by Epic - #5 by ilze.mezite

But it only partially solves my problem.
I want to do a cumulative sum per fix version in one table (and exclude the tasks):

image
image1201×412 29.9 KB

So in the table shown I want to sum only items that have the same “Issue Fix Version Selected”. So somehow I need to reset the sum when a new “Issue Fix Version Selected” is encountered (which I tried to do as explained in my original post).

My original solution tries to replace the cumulative sum by the current remining estimated hours when a nee fix version is encountered (but this does work only sometimes as expected):

IIf([Measures].[Issue type]='Task',0,
-- same fix version: cumulate
  IIf(Cast(PreviousRowValue([Measures].[Issue Fix Versions Selected]) as String)
  =Cast([Measures].[Issue Fix Versions Selected] as String)
  ,
--different version.."reset" cumulated value
  CumulativeSum([Measures].[Remaining estimated hours])
  ,
  [Measures].[Remaining estimated hours]
  )
)

If I could compute the cumulativesum myslef in a measure this would be easy, unfortunately this is forbidden:

[Measures].[Remaining estimated hours]+PreviousRowValue([Measures].[name of current measure])

Here is an example what I want to get out:

Epic | Remaining Hours | Fix Version | CumSumPer Fix Version

EP-1| 10 | FV1| 10
EP-2| 11 | FV1| 21 --10+11
EP-3| 12 | FV2| 12 --new fixversion, reset cum sum to remaining hours of first epic in fixvesrion
EP-4| 13 | FV2| 25 – 12+13
EP-5| 14 | FV3| 14 --new fixversion, reset cum sum

3

Seems the same question is asked here: Cumulative sum of calculated measure for tasks grouped by Epic - #6 by jakedatreble

Sorry for the duplicate.

4

Hi @aherz,

The main benefit of cumulative sum is the ability to handle the inexplicit order of members according to sorting, filtering, or manual removal of entries.
It also allows us to see where the required resources reach the limit threshold.

However, that causes trouble when you want to create cumulative sub-totals resetting multiple times over the displayed list of entries.

Addressing the same measure from itself poses the risk of an infinite loop or excessive recursive loops for longer continuous lists of displayed members. Therefore, it is forbidden.

If you have no specific conditions for ordering the members and are looking for the cumulative sum for the epics - you might iterate through the siblings of the displayed member to find the cumulative sum for the siblings thus far, respecting the original order of siblings according to their creation.

If each version only appears in one continuous block of siblings, you might use the different hierarchies of the Issue dimension to “save” the current row.
​
​In that case, the expression for finding the cumulative sum of story points for Epics having the same Issue Fix Versions Selected might be something of the kind.

CASE WHEN
 [Measures].[Issues created]>0
THEN
Sum(
--switching hierarchy to "save" current issue
 [Issue].[Issue].GetMemberByKey(
   [Issue.Epic].CurrentHierarchyMember.Key),
--numeric value - value from filtered rows
Sum(
--filter relevant previous members
 Filter(
--set of epics thus far
  {
  [Issue.Epic].CurrentMember.FirstSibling:
  [Issue.Epic].CurrentMember
  },
--relevant siblings
  ([Measures].[Issues created],
--reset the default hierarchy to ignore "saved" issue
   [Issue].DefaultMember)>0
   AND
--compare versions 
   CAST(
    DefaultContext(
   ([Measures].[Issue Fix Versions Selected],
--reset Epics hierarchy to see value of "saved" issue
   [Issue.Epic].CurrentMember)) as string)
   =
   Cast(
   DefaultContext(
   ([Measures].[Issue Fix Versions Selected],
--reset the default hierarchy "saved" issue to see the value of sibling being evaluated
    [Issue].CurrentMember)) as string)
 ),
 ([Measures].[Story Points created],
--reset "saved" issue from default hierarchy
  [Issue].DefaultMember)
 )
--end of switching hierarchies
)
END

However, the viability of this approach depends on multiple additional conditions and the actual setup.
I suggest contacting support directly with a full description of the situation and all the details, including all custom calculations that make up your report, to find a reasonable approach.

Regards,
Oskars / support@eazyBI.com

5

Hi Oskars,

thanks for the suggestion. Unfortunately, it is even slower then my current solution, see Cumulative sum of calculated measure for tasks grouped by Epic - #7 by aherz