In this Tutorial, we will go over the creation of the Velocity Variance and Story Size KPIs.
Link to Tutorial: https://youtu.be/JIxDsuVoRg0
Definitions:
What Velocity Variance?
What is Story Size?
Formulas
Velocity Variance
Story Size
Other topics covered in the tutorial:
Thanks,
Eli Solutions
I tried to import the export definitions and I am getting an error…
Report definition is in wrong format.
Can anyone please assist…
Make sure you copy from the beginning of the first “{” to the last one “}”.
Same error to me “Report definition is in wrong format.”
I copied correctly the definition from the beginning of the first “{” to the last one “}”.
Reposting in code tags.
Velocity Variance
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Velocity Variance",
"folder_name": "Agile Reports",
"result_view": "line_chart",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Velocity Variance]","[Measures].[Upper Bound]","[Measures].[Lower Bound]"],"members":null}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[Sprint].Members"],"members":null,"bookmarked_members":null}],"filter_by":{"conditions":[{"expression":["[Measures].[Sprint Story Points committed]"],"operator":"\u003e","value":"0"}]}},"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":"Project Marvel ","full_name":"[Project].[Project Marvel ]","drillable":true,"key":"MARVEL","parent_full_name":"[Project].[All Projects]"}],"bookmarked_members":null,"current_page_members":["[Project].[Project Marvel ]"]}]},"options":{},"view":{"current":"line_chart","maximized":false,"line_chart":{"area":false,"swap_axes":false,"data_labels":true,"series_options":{"Upper Bound":{"type":"area","color":"#CAF8FF","dataLabelType":false},"Lower Bound":{"type":"area","color":"#CAF8FF","dataLabelType":false},"Velocity Variance":{"type":"spline"}},"x_axis":{"0":{"title":"Sprint Name"}},"y_axis":{"0":{"title":"Percentage"}}},"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":"Sprint Story Points at closing","dimension":"Measures","format_string":"#,##0.00","formula":"-- Story points from issues when a sprint was closed\n( [Measures].[Story Points added],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint Story Points completed","dimension":"Measures","format_string":"#,##0.00","formula":"(\n [Measures].[Sprint Story Points at closing],\n [Transition Status.Category].[Done]\n)\n"},{"name":"Running Story Points velocity for 5 closed sprints","dimension":"Measures","format_string":"#,##0.00","formula":"CASE\n WHEN\n [Sprint].CurrentHierarchyMember.Level.Name = 'Sprint' AND\n [Sprint].CurrentHierarchyMember.GetBoolean('Closed')\n THEN\n AVG(\n Tail(\n Filter(\n -- filter last 5 previous sprints in a board starting from current sprint\n [Sprint].CurrentHierarchyMember.FirstSibling:\n [Sprint].CurrentHierarchyMember,\n [Sprint].CurrentHierarchyMember.GetBoolean('Closed')\n ),\n -- set the count of last closed sprints for running velocity\n 5 ) ,\n [Measures].[Sprint Story Points completed]\n )\nEND\n"},{"name":"Velocity Variance","dimension":"Measures","formula":"-- Velocity Variance = ([5 Sprint Rolling Avg. Velocity – Current Velocity])/ 5 Sprint Rolling Avg. Velocity\n([Measures].[Running Story Points velocity for 5 closed sprints]-[Measures].[Sprint Story Points completed])\n/[Measures].[Running Story Points velocity for 5 closed sprints]","format_string":"#0.00%"},{"name":"Upper Bound","dimension":"Measures","formula":"0.10","format_string":"#0.00%"},{"name":"Lower Bound","dimension":"Measures","formula":"-0.10","format_string":"#0.00%"}]
}
Story Size
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Story Size",
"folder_name": "Agile Reports",
"result_view": "bar_chart",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Story Size]"],"members":null}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[Sprint].Members"],"members":null,"bookmarked_members":null}],"filter_by":{"conditions":[{"expression":["[Measures].[Sprint Story Points committed]"],"operator":"\u003e","value":"0"}]}},"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":"Project Marvel ","full_name":"[Project].[Project Marvel ]","drillable":true,"key":"MARVEL","parent_full_name":"[Project].[All Projects]"}],"bookmarked_members":null,"current_page_members":["[Project].[Project Marvel ]"]}]},"options":{},"view":{"current":"bar_chart","maximized":false,"bar_chart":{"stacked":false,"vertical":true,"swap_axes":false,"data_labels":true,"series_options":{},"x_axis":{"0":{"title":"Sprint Name"}},"y_axis":{"0":{"title":"Story Points"}}},"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":"Sprint Story Points at closing","dimension":"Measures","format_string":"#,##0.00","formula":"-- Story points from issues when a sprint was closed\n( [Measures].[Story Points added],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint Story Points completed","dimension":"Measures","format_string":"#,##0.00","formula":"(\n [Measures].[Sprint Story Points at closing],\n [Transition Status.Category].[Done]\n)\n"},{"name":"Sprint issues at closing","dimension":"Measures","format_string":"#,##0","formula":"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n -- An issue was in a sprint at closing\n [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint issues completed","dimension":"Measures","format_string":"#,##0","formula":"(\n [Measures].[Sprint issues at closing],\n [Transition Status.Category].[Done]\n)\n"},{"name":"Story Size","dimension":"Measures","formula":"-- Sprint Story points completed/Sprint Issues Completed\nCASE WHEN IsEmpty([Measures].[Sprint issues completed]) THEN\n 0\nELSE\n [Measures].[Sprint Story Points completed]/\n [Measures].[Sprint issues completed]\nEND","format_string":"#,##0.00"}]
}
It’s works!!
Thanks Eli!!
hello,
what if I would determine story size by using just closed issues with story points and not all the closed issues?
regards