Summing the columns

[davidstokesnz]

Hi

I’m new to EasyBI, and am finding it powerful.

We are using tempo timesheets for recording time and are measuring the original estimate, timespent and remaining estimate as well as forecasting the final hours (timespent + remaining hours)
We are using the hierarchy of project → epic → story → subtask and the hours should rollup up:
Story hours = hours on the story + the hours spent on the subtasks
Epic hours = hours on the Epic + Story hours as above
Project hours = hours on all the epics as above

What I would like to do is create a line graph over time (week by week over the project) showing the accumulated original estimates hours by each epic, the accumulated hours spent by each epic, the accumulated remaining hours and hours spent by each epic, the forecasted final hours.

I’ve attached a drawing of what I’m wanting to create based on the data from Jira and Tempo.

My last hurdle is calculating the final estimate which is a sum of the column: Cumulative Hours spent with subtasks and Remaining estimated hours history using a calculated measure:
[Measures].[ Forecasted Final ] =
[Measures].[Hours spent with sub-tasks] +
[Measures].[Remaining estimated hours change]

But it needs to be of the Cumulative Hours spent with subtasks - I’m not sure how to do this in a calculated member formula.

I know I’m close, but I’d appreciate it if someone can point me in the right direction so I don’t spend another couple of hours working it out.

Thanks
David

[davidstokesnz]

I’ve got pretty close to it through trial and error.

[davidstokesnz]

Here’s the table:

[davidstokesnz]

Here’s the definition
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Project Report”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Cumulative Original estimated hours with sub-tasks]","[Measures].[Cumulative Hours spent with sub-tasks]","[Measures].[Remaining estimated hours history]","[Measures].[Forecasted Final]"],“members”:[{“depth”:0,“name”:“Cumulative Original estimated hours with sub-tasks”,“full_name”:"[Measures].[Cumulative Original estimated hours with sub-tasks]",“drillable”:false,“calculation_of”:"[Measures].[Original estimated hours with sub-tasks]",“calculation”:“cumulative_sum”,“format_string”:"#,##0.00"},{“depth”:0,“name”:“Cumulative Hours spent with sub-tasks”,“full_name”:"[Measures].[Cumulative Hours spent with sub-tasks]",“drillable”:false,“calculation_of”:"[Measures].[Hours spent with sub-tasks]",“calculation”:“cumulative_sum”,“format_string”:"#,##0.00"}]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time.Weekly].[Week].Members"],“members”:null,“bookmarked_members”:null}],“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“earSHOTS”,“full_name”:"[Project].[earSHOTS]",“drillable”:true,“key”:“EST”,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:null,“current_page_members”:["[Project].[earSHOTS]"]},{“name”:“Issue”,“selected_set”:["[Issue.Epic].[All Issues by epics]"],“members”:[{“depth”:0,“name”:“All Issues by epics”,“full_name”:"[Issue.Epic].[All Issues by epics]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:null,“current_page_members”:["[Issue.Epic].[All Issues by epics]"]}]},“options”:{},“view”:{“current”:“table”,“maximized”:false,“table”:{}},“calculated_members”:null}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Original estimated hours with sub-tasks”,“format_string”:"#,##0.00",“formula”:"[Measures].[Original estimated hours] +\n[Measures].[Sub-tasks original estimated hours]"},{“dimension”:“Measures”,“name”:“Hours spent with sub-tasks”,“format_string”:"#,##0.00",“formula”:"[Measures].[Hours spent] +\n[Measures].[Sub-tasks hours spent]"},{“dimension”:“Measures”,“name”:“Remaining estimated hours history”,“format_string”:"#,##0.00",“formula”:“Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n [Measures].[Remaining estimated hours change]\n ))\n + [Measures].[Remaining estimated hours change]\n)”},{“name”:“Forecasted Final”,“dimension”:“Measures”,“formula”:"[Measures].[Hours spent with sub-tasks] + \n[Measures].[Remaining estimated hours change]",“format_string”:""}]
}

[lauma.cirule]

Hi @davidstokesnz,

You are very close, truly! If you define a report specific measure, even though the typeahead does not offer the standard calculations, as long as you also have this measure used in the graph, you can reference it by name:

[Measures].[Cumulative Hours spent with sub-tasks] +
[Measures].[Remaining estimated hours change]

Adding a generic user-defined measure with reference to report specific calculations is not possible. User-defined measures are available from other reports, but the standard calculation is specific to the report where it is defined.

Lauma / support@eazybi.com