We use a custom field in our Jira for investment type. With sprint planning we want to aim for a certain percentage of each of the three investment types we have. What I am looking for is a report of the most recent sprint that breaks down how many story points are committed and how many per each investment type. I am running into issues getting just a sprint to display and then furthermore with getting committed points to display.
@Paula_Pokusa
Make sure you import “Investmenty type” field as separate dimension, then you could create a calculated member in “Sprint” dimension to always return just the last sprint (that has started).
Aggregate(
Generate(
-- get all boards
[Sprint].[Board].Members,
-- access all children - sprints from the board
Tail(
Filter(
[Sprint].CurrentMember.Children,
Not IsEMpty([Sprint].CurrentMember.get("Start date"))
)
,1
)
-- address the first (0) member from a set
.Item(0)
)
)
Finally, create a calculated measure (with integer % formatting) to calculate the percentage of committed story points by each investment type.
CASE WHEN [Measures].[Sprint Story Points committed] = 0 THEN 0
WHEN NOT IsEmpty([Measures].[Sprint Story Points committed]) THEN
[Measures].[Sprint Story Points committed] / ([Measures].[Sprint Story Points committed], [Investment Type].DefaultMember)
END
See the picture below where I used Component instead of Investment type.
Martins / eazyBI
Hi Martins,
I was able to confirm we do have “Investmenty type” field as separate dimension already in our instance. I was also able to import that new calculated member as well as the new calculated measure. Sadly, something is still terribly wrong on my end as you can see from the screenshot. Appreciate the help and any further troubleshooting would be much appreciated!
Why don’t you use the “Investmenty type” dimension in columns as secondary dimension?
Then it would be enough with just one calculated measure in “Measures” dimension using this code:
CASE WHEN [Measures].[Sprint Story Points committed] = 0 THEN 0
WHEN NOT IsEmpty([Measures].[Sprint Story Points committed]) THEN
[Measures].[Sprint Story Points committed] / ([Measures].[Sprint Story Points committed], [Investment Type].DefaultMember)
END
But likely this is not the real problem as “Story points committed” is empty which shouldn’t be the case.
Please enable “Nonempty” cross join for report rows: Create reports
If that didn’t show just the last sprint please export and share the definition for your report.
Martins / eazyBI
Somehow that got very much worse 
Here is the definition after those changes:
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “% Investment Types - Last Sprint”,
“folder_name”: “Paula’s Folder”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Story Points Committed]"],“members”:null},{“name”:“Investment Type”,“selected_set”:["[Investment Type].[All Investment Types]"],“members”:null,“bookmarked_members”:null}]},“rows”:{“dimensions”:[{“name”:“Sprint”,“selected_set”:["[Sprint].[Last Sprint]"],“selected_set_expression”:“DescendantsSet({{selected_set}}, [Sprint].[Sprint])”,“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”:“Docs Disco”,“full_name”:"[Project].[Docs Disco]",“drillable”:true,“key”:“DD”,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:null,“current_page_members”:["[Project].[Docs Disco]"]},{“name”:“Sprint”,“duplicate”:true,“selected_set”:["[Sprint].[Last Sprint]"],“members”:[{“depth”:0,“dimension”:“Sprint”,“name”:“Last Sprint”,“formula”:“Aggregate(\n Generate(\n – get all boards\n [Sprint].[Board].Members,\n – access all children - sprints from the board\n Tail(\n Filter(\n [Sprint].CurrentMember.Children,\n Not IsEMpty([Sprint].CurrentMember.get(“Start date”))\n )\n ,1\n )\n – address the first (0) member from a set\n .Item(0)\n )\n)”,“format_string”:"",“full_name”:"[Sprint].[Last Sprint]",“drillable”:true,“annotations”:null,“calculated”:true,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:null,“current_page_members”:["[Sprint].[Last Sprint]"]}]},“options”:{“nonempty”:“rows”},“view”:{“current”:“table”,“maximized”:false,“table”:{}},“calculated_members”:null}
} ],
“calculated_members”: [{“name”:“Sprint Story Points committed”,“dimension”:“Measures”,“format_string”:"#,##0.00",“formula”:"( [Measures].[Story Points added],\n [Transition Field].[Sprint status],\n [Sprint Status].[Active],\n – An issue was in a sprint at a sprint start time\n [Issue Sprint Status Change].[Future =\u003e Active]\n)\n"},{“name”:“Last Sprint”,“dimension”:“Sprint”,“formula”:“Aggregate(\n Generate(\n – get all boards\n [Sprint].[Board].Members,\n – access all children - sprints from the board\n Tail(\n Filter(\n [Sprint].CurrentMember.Children,\n Not IsEMpty([Sprint].CurrentMember.get(“Start date”))\n )\n ,1\n )\n – address the first (0) member from a set\n .Item(0)\n )\n)”,“format_string”:""},{“name”:“Story Points Committed”,“dimension”:“Measures”,“formula”:“CASE WHEN [Measures].[Sprint Story Points committed] = 0 THEN 0\n WHEN NOT IsEmpty([Measures].[Sprint Story Points committed]) THEN\n [Measures].[Sprint Story Points committed] / ([Measures].[Sprint Story Points committed], [Investment Type].DefaultMember)\n END”,“format_string”:"#0%"}]
}
And what it now looks like
Seems something is wrong with the “Last sprint” member.
Does it show any results if you remove the “Project” filter?
Anyway, I think it would be better if you reach out to eazyBI support and we will help you understand the reasons why it appears empty now.
Martins / eazyBI