I am working on an EazyBI report with department projects aligned to JIRA Epics. Each JIRA epic has tasks categorized as Small, Medium and Large tasks. I am trying to get the cumulative sum of a calculated measure called ‘costs’ for all the tasks grouped by Epic.
The MDX formula that I came up with uses the CurrentTuple(VisibleRowsSet())function provides me with the correct cumulative sum value when the table is collapsed to the JIRA Project level. However, when the table is expanded, the formula fails and the final calculated value also includes the sum calculated at the Project level.
Can someone help me with a formula to resolve this and additionally calculate the total cost for tasks grouped by Epics. Basically a sub-total for each separate JIRA Epic.
I hope the screenshots provided below helps explain my situation.
Some of the things I have tried:
CASE WHEN
[Measures].[Actual Count closed] > 0
AND
CoalesceEmpty([Issue].CurrentMember.Get(‘Epic Name’),“”) MATCHES
CoalesceEmpty([Issue].CurrentMember.PrevMember.Get(‘Epic Name’),“”)
THEN
CumulativeSum(
[Measures].[Categorized Total Cost - All T-Shirt Sizes]
)
END
The above returns a false for the boolean expression. But when I use [Measures].[Issue Epic Name]as below, at least the expression evaluates to a true where there is a match but I still run into the issue with the cumulative sum.
CASE WHEN
CoalesceEmpty([Measures].[Issue Epic Name],“”) MATCHES
CoalesceEmpty([Measures].[Previous Row Epic Name],“”)
THEN
CumulativeSum(
[Measures].[Categorized Total Cost - All T-Shirt Sizes]
)
END
Thanks,
Screenshot highlighting failure (in red) when JIRA project is expanded to show Epics/tasks.
Welcom to the eazyBI community. Thank you for the detailed description and the screenshot. It’s helpful.
To have a cumulative sum by Epic level and then a cumulative sum summed up on the higher - Project level, try using this formula:
CASE WHEN
[Issue].CurrentHierarchyMember.level.name = "Epic"
THEN
CumulativeSum(
[Measures].[Issues created]
)
WHEN
[Issue].CurrentHierarchyMember.level.name = "Project"
THEN
CumulativeSum(
[Measures].[Issues created]
)
END
This formula will work if the “Epic” hierarchy is selected in the Issue dimension.
Thanks much Mezite for your response. Your solution helped with issue #1 which I mentioned where the cumulative addition includes the values from Project.
Could you help me with the the second issue, where I need to calculate the cumulative sum only for all the tasks that belong to the same epic. If there is an expression that will uniquely filter out each row by the epic name and calculate the cumulative total, that will do the job I feel.
The formula I gave you is for the Project and Epic levels using the Issues dimensions hierarchy level - Epic. If you need to go down one level, the next level is “Parent”. See the picture below:
In my example, I’m creating the cumulative sum for the: Project, Epic, and Tasks using measure to sum - Issues created. Please replace the Issues created measure with the correct one (“Categorized Total Cost - All T-Shirt Sizes”):
CASE WHEN
[Issue].CurrentHierarchyMember.level.name = "Parent"
THEN
CumulativeSum(
[Measures].[Issues created]
)
WHEN
[Issue].CurrentHierarchyMember.level.name = "Epic"
THEN
CumulativeSum(
[Measures].[Issues created]
)
WHEN
[Issue].CurrentHierarchyMember.level.name = "Project"
THEN
CumulativeSum(
[Measures].[Issues created]
)
END
Hi Ilze, thanks for the response. Sadly this doesn’t resolve my issue either. I am attaching another screenshot straight from EazyBI showing highlighting the issue - still not getting the cumulative total just for each epic separately using the new formula. Perhaps I need to change the overall structure of the table? Thanks!
I have mostly a solution for this, I compute the sum for the cost of all tasks in each epic in a measure (gives the same value for each row/task that is in the same epic).
Then I use PreviousRowValue() to see when the epic has changed to a new one and substract the sum of all tasks in the previous row epic from the cumulative sum, this resets the cumulative sum to 0.
The adding of the current task cost and the subtraction of the previous epic cost has to happen inside the same CumulativeSum() expression in your measure (if you split this up into different CumulativeSum() calls then the different calls will not affect each other giving weird results), so it will look something like this:
Cast(CumulativeSum(
-- first the normal accumulation
[Measures].[Task Cost]-
-- subtract the previous epic cost, in case this is the first row in a new epic
IIf(PreviousRowValue([Measures].[Epic Name])<>[Measures].[Epic Name] -- PreviousRowValue may have a bug at the moment..discussing this with eazybi
,[Measures].[PreviousRowWholeEpicCost],0)
) as NUMERIC) --need to cast or IIf gets confused with types
To compute [Measures].[CurrentRowWholeEpicCost] you have to Sum the cost of all tasks in the given Epic. I would want to compute this by something like this:
-- pseudo code
Sum(
Filter(
VisibleRowSet(), --this has all the tasks and epic I care about, do not want to start with a slow [Issue].Members here
[Issue].CurrentMember.get('Epic Link') -- what is this addressing? the current item in VisibleRowSet iteration or the item in the current table row?
=
[Issue.Epic].CurrentMember.key -- same here, this seems to address the current item in the visible row set whereas I would want to address the current epic in my table
),
[Measures].[Task Cost]
)
And [Measures].[PreviousRowWholeEpicCost] is simply
I got this to work by using ‘[Issue].Members’ (then the VisibleRowSet() and the issues one iterates over have a different hierarchy and can be addressed by that, so do not shadow each other) and applying some filters to the [Issue].Memebrs (only not closed epics) to get it reasonable fast…but it is still somewhat slow and it would be really good to understand how to filter parts of the VisibleRowSet() based on the current entry in the table (even if they have the same dimension).
Please note that there seems to be a possible bug in PreviousRowValue() (or I am using it wrong) which leads to my solution being flaky (PreviousRowValue() seems to return different results in the same table row with the same expression). Also PreviousRowValue() seems sometimes to return a non empty value for the first row in the table which also leads to problems.
Additional note, this would be much simpler to achieve if one could compute the CumulativeSum using PreviousRowValue() like this:
So I got feedback from easybi (thx Ilze) that using the same dimension and hierarchy twice (in on filter) is not possible and that the following is pretty much the best way to compute [Measures].[CurrentRowWholeEpicCost]
Sum(
Filter(
Filter(
[Issue].Members,
[Measures].[Issue type]='Epic'
-- I could reduce this even more by using only epics that are not closed and in a specific project and moving all that into a computed member [Issue].[Open PLENG Epics] in the Issues and then address it as Cache(CalculatedChildrenSet([Issue].[Open PLENG Epics]))
)
,
Intersect(
[Fix Version].[Version].GetMembersByKeys([Issue.Epic].CurrentMember.Get('Fix version IDs')),
[Fix Version].[Version].GetMembersByKeys([Issue].CurrentMember.Get('Fix version IDs'))
).Count > 0
)
,
DefaultContext((
[Issue.Epic].[Epic].GetMemberByKey([Issue].CurrentMember.key),
[Measures].[Remaining estimated hours]
))
)
This is pretty fast and now my solution is complete in principle. Unfortunately, the bug in PreviousRowValue was confirmed so the solution is still flaky until that is fixed.
Hi Jake,
No problem, I am mostly doing this for my own reporting needs
Please be aware that PreviousRowValue() is flaky as I write further up.
To make the first row not flaky, this can be used to set a defined value for the first row:
CASE WHEN
CurrentTuple(VisibleRowsSet()) IS -- is this the first row?
VisibleRowsSet().Item(0)
THEN
-- yes it is, provide a good default for the first row
""
ELSE
--other row expression
PreviousRowValue([Measures].[Issue Fix Version Selected Number])
END
This is still flakey but less so. AFAIK, easybi is working to fix the flakyness, until it is fixed the report may randomly show wrong data in the cumulative sum.
With eayzbi 7.1.1 the flaky behavior of PreviousRowValue is fixed. So the workaround above is not needed anymore (and actually caused problems in my reports). My table now works as expected.
I came across one more interesting thing. In a table with epics in rows and fix versions in pages the following 2 are mostly but not always the same:
and just using “[Measures].[Remaining estimated hours]”.
[Measures].[Remaining estimated hours] will filter out issues in the epic that do not match the pages filter (so lower result) whereas the DefaultContext… includes all items from the epic.
I have no idea how to include the pages filter in the DefaultContext.
