# Agile Reporting: Velocity Variance and Story Size

**URL:** <https://community.eazybi.com/t/agile-reporting-velocity-variance-and-story-size/6684>\
**Category:** Tips & Tricks\
**Tags:** agile, report-specific, jira\
**Created:** [April 24, 2021, 6:04pm UTC](https://community.eazybi.com/t/agile-reporting-velocity-variance-and-story-size/6684 "2021-04-24T18:04:07Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![eli\_solutions](https://community.eazybi.com/user_avatar/community.eazybi.com/eli_solutions/32/4822_2.png) [@eli\_solutions](https://community.eazybi.com/u/eli_solutions)\
**Post date:** [April 24, 2021, 6:04pm UTC](https://community.eazybi.com/t/agile-reporting-velocity-variance-and-story-size/6684/1 "2021-04-24T18:04:07Z")

</div>

![Youtube Card - Final](https://community.eazybi.com/uploads/default/original/2X/1/11a406ed70afef27f0aae285b3f69be7af34beb2.jpeg)

In this Tutorial, we will go over the creation of the Velocity Variance and Story Size KPIs.

Link to Tutorial: [https://youtu.be/JIxDsuVoRg0](https://youtu.be/JIxDsuVoRg0)

**Definitions:**

What Velocity Variance?

 ![image](https://community.eazybi.com/uploads/default/original/2X/e/e679d4ff6a178c00595270be7386544b821d659f.png)

What is Story Size?

 ![image](https://community.eazybi.com/uploads/default/original/2X/2/22f3bf1ebddff1fc2bded5f4026401f6787563d0.png)

**Formulas**

- Velocity Variance  

- Story Size  

**Other topics covered in the tutorial:**

- CASE Statement
- IsEmpty Function

Thanks,

Eli Solutions

---

<div class="post-metadata">

**Author:** ![morjo02](https://community.eazybi.com/letter_avatar_proxy/v4/letter/m/b19c9b/32.png) [@morjo02](https://community.eazybi.com/u/morjo02)\
**Post date:** [August 6, 2021, 1:25am UTC](https://community.eazybi.com/t/agile-reporting-velocity-variance-and-story-size/6684/3 "2021-08-06T01:25:16Z")

</div>

I tried to import the export definitions and I am getting an error…

Report definition is in wrong format.

Can anyone please assist…

---

<div class="post-metadata">

**Author:** ![eli\_solutions](https://community.eazybi.com/user_avatar/community.eazybi.com/eli_solutions/32/4822_2.png) [@eli\_solutions](https://community.eazybi.com/u/eli_solutions)\
**Post date:** [August 10, 2021, 7:26pm UTC](https://community.eazybi.com/t/agile-reporting-velocity-variance-and-story-size/6684/4 "2021-08-10T19:26:11Z")

</div>

Make sure you copy from the beginning of the first “{” to the last one “}”.

---

<div class="post-metadata">

**Author:** ![Wanderson](https://community.eazybi.com/letter_avatar_proxy/v4/letter/w/439d5e/32.png) [@Wanderson](https://community.eazybi.com/u/Wanderson)\
**Post date:** [December 3, 2021, 10:00pm UTC](https://community.eazybi.com/t/agile-reporting-velocity-variance-and-story-size/6684/5 "2021-12-03T22:00:09Z")

</div>

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 “}”.

---

<div class="post-metadata">

**Author:** ![eli\_solutions](https://community.eazybi.com/user_avatar/community.eazybi.com/eli_solutions/32/4822_2.png) [@eli\_solutions](https://community.eazybi.com/u/eli_solutions)\
**Post date:** [December 4, 2021, 11:09pm UTC](https://community.eazybi.com/t/agile-reporting-velocity-variance-and-story-size/6684/7 "2021-12-04T23:09:16Z")

</div>

Reposting in code tags.

Velocity Variance

```auto
{
  "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

```auto
{
  "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"}]
}

```

---

<div class="post-metadata">

**Author:** ![Wanderson](https://community.eazybi.com/letter_avatar_proxy/v4/letter/w/439d5e/32.png) [@Wanderson](https://community.eazybi.com/u/Wanderson)\
**Post date:** [December 6, 2021, 10:08pm UTC](https://community.eazybi.com/t/agile-reporting-velocity-variance-and-story-size/6684/8 "2021-12-06T22:08:05Z")

</div>

It’s works!!

Thanks Eli!!

---

<div class="post-metadata">

**Author:** ![Mauro\_Bennici](https://community.eazybi.com/user_avatar/community.eazybi.com/mauro_bennici/32/2043_2.png) [@Mauro\_Bennici](https://community.eazybi.com/u/Mauro_Bennici)\
**Post date:** [April 19, 2024, 1:19pm UTC](https://community.eazybi.com/t/agile-reporting-velocity-variance-and-story-size/6684/9 "2024-04-19T13:19:05Z")

</div>

hello,  
what if I would determine story size by using just closed issues with story points and not all the closed issues?  
regards