EazyBI - Average between sprints

We have the following "report configuration":

"cod requerimentos" is a Custom Field that have sprints within it. Each sprint inside a "cod requerimentos" have a IEP Sprint (calculated measure). 

We need to calculate the average of those "IEP Sprint" by "Sprint category" (total, closed sprints, and so on), and this average needs to be shown in "IEP Proyecto" measure. For example, in the image below we need that the "Total" row for "(none)" shows the average of the 5 "IEP Sprint" measures within it ((80+95+100+100+100)/5), and we need that the "Closed Sprints" row for the "(none)" shows the average of the 13 "IEP Sprint" measures within it.

This is the current formula for "IEP Proyecto" and it is showing the TOTAL AVERAGE, of all sprints, independently of the "cod requirementos". I just need to "segment" this measure and present the results by "Sprint category" and by "cod requerimentos".

--annotations.group=PMO Agile
CASE
WHEN
[Sprint].CurrentMember.Level.name <> "Sprint"
THEN
NonZero(
Avg(
Filter( [Sprint].[Sprint].Members,
Not IsEmpty([Sprint].CurrentMember.get('Start date'))
AND
[Measures].[IEP Sprint]>0

),
[Measures].[IEP Sprint]
)
)
END
END

 

Screen Shot 2017-12-05 at 1.52.27 PM.png

In case that you need the report definition:

{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Tablero Agile Vice Presidencia testing",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[IEPG Agile]","[Measures].[IEP Proyecto]","[Measures].[IEP Sprint]","[Measures].[Avg of Desviaci\u00f3n Agile]","[Measures].[Sprint start date]","[Measures].[Sprint end date]","[Measures].[Sprint complete date]","[Measures].[Today Date]","[Measures].[Sprint Planned Days]","[Measures].[Sprint Total Days]","[Measures].[Cod Requerimiento counter]"],"members":null}]},"rows":{"dimensions":[{"name":"Cod Requerimiento","selected_set":["[Cod Requerimiento].[Total All cod requirementos]","[Cod Requerimiento].[All Cod Requerimientos]"],"members":[{"depth":0,"name":"All Cod Requerimientos","full_name":"[Cod Requerimiento].[All Cod Requerimientos]","drillable":true,"type":"all","expanded":true,"drilled_into":false,"removed":true}],"bookmarked_members":null},{"name":"Sprint","selected_set":["[Sprint].[Total]","[Sprint].[Next Future sprints]","[Sprint].[Closed sprints]"],"members":[{"depth":0,"name":"Total","full_name":"[Sprint].[Total]","calculated":true,"drillable":true,"expanded":true,"drilled_into":false,"id":"[Sprint].[Total]","format_string":""},{"depth":0,"name":"Closed sprints","full_name":"[Sprint].[Closed sprints]","calculated":true,"drillable":true,"expanded":true,"drilled_into":false}],"bookmarked_members":null}],"filter_by":{"conditions":[{"expression":["[Measures].[Issues created]"],"operator":">","value":"0"},{"expression":["[Measures].[Hiding projects from total]"],"operator":">","value":"0"}]},"nonempty_crossjoin":true},"pages":{"dimensions":null},"options":{},"view":{"current":"table","maximized":false,"table":{"cell_formatting":{"[Measures].[Avg of Desviaci\u00f3n Agile]":[{"max":"0","background_color":"#AA4643"},{"min":"0","background_color":"#89A54E"}]}}}}
} ],
"calculated_members": [{"name":"Sprint start date","dimension":"Measures","format_string":"mmm dd yyyy","annotations":{"group":"Agile"},"formula":"[Sprint].CurrentMember.get('Start date')"},{"name":"Sprint end date","dimension":"Measures","format_string":"mmm dd yyyy","annotations":{"group":"Agile"},"formula":"[Sprint].CurrentMember.get('End date')"},{"name":"Sprint complete date","dimension":"Measures","format_string":"mmm dd yyyy","annotations":{"group":"Agile"},"formula":"[Sprint].CurrentMember.get('Complete date')"},{"name":"Closed sprints","dimension":"Sprint","format_string":"","formula":"Aggregate(\n Order(\n Filter([Sprint].[Sprint].Members,\n [Sprint].CurrentMember.getBoolean('Closed') AND\n NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))),\n [Sprint].CurrentMember.get('Start date'),\n BASC\n )\n)"},{"name":"Sprint Total Days","dimension":"Measures","formula":"--annotations.group=PMO Agile\nCASE [Sprint].CurrentMember.getBoolean('Closed')\nWhen CBool(1) -- yes calculate with complete date\n Then\n DateDiffDays([Measures].[Sprint start date],[Measures].[Sprint complete date])\nWhen CBool(0) -- no calculate with today date\n Then\n DateDiffDays([Measures].[Sprint start date],[Measures].[Today Date])\nEnd","format_string":"#,##0"},{"name":"Today Date","dimension":"Measures","formula":"now()","format_string":"mmm dd yyyy"},{"name":"Sprint Planned Days","dimension":"Measures","formula":"--annotations.group=PMO Agile\nDateDiffDays([Measures].[Sprint start date],[Measures].[Sprint end date])","format_string":"#,##0"},{"name":"IEP Sprint","dimension":"Measures","formula":"--annotations.group=PMO Agile\nCASE WHEN [Cod Requerimiento].CurrentMember.Level.Name = 'Cod Requerimiento' THEN\n CASE\n WHEN [Measures].[Sprint Total Days] <= [Measures].[Sprint Planned Days]\n THEN 1\n ---Sprint Total Days <= Sprint Planned Days + 10%\n WHEN [Measures].[Sprint Total Days] <= [Measures].[Sprint Planned Days] * 1.1\n THEN 0.95\n ---Sprint Total Days <= Sprint Planned Days + 15%\n WHEN [Measures].[Sprint Total Days] <= [Measures].[Sprint Planned Days] * 1.15\n THEN 0.9\n ---Sprint Total Days <= Sprint Planned Days + 16%\n WHEN [Measures].[Sprint Total Days] <= [Measures].[Sprint Planned Days] * 1.16\n THEN 0.899\n ---Sprint Total Days > Sprint Planned Days + 17%\n WHEN [Measures].[Sprint Total Days] > [Measures].[Sprint Planned Days] * 1.17\n THEN 0.8\n ELSE null\n END\nEND","format_string":"#0.00%"},{"name":"IEP Proyecto","dimension":"Measures","formula":"--annotations.group=PMO Agile\n CASE \n WHEN\n [Sprint].CurrentMember.Level.name <> \"Sprint\"\n THEN\n NonZero(\n Avg(\n Filter( [Sprint].[Sprint].Members,\n Not IsEmpty([Sprint].CurrentMember.get('Start date'))\n AND\n [Measures].[IEP Sprint]>0\n\n ),\n [Measures].[IEP Sprint]\n )\n )\n END\nEND","format_string":"#0%"},{"name":"Avg of Desviaci\u00f3n Agile","dimension":"Measures","formula":"--annotations.group=PMO Agile\nCASE \nWHEN\n [Sprint].CurrentMember.Level.name = \"Sprint\"\nTHEN\n val([Measures].[Desviaci\u00f3n Agile])\nELSE\n NonZero(\n Avg(\n Filter([Sprint].[Sprint].Members,\n Not IsEmpty([Sprint].CurrentMember.get('Start date'))\n AND\n [Measures].[Issues created]>0\n -- AND\n --[Measures].[Desviaci\u00f3n Agile] > 0\n ),\n [Measures].[Desviaci\u00f3n Agile]\n )\n )\nEND","format_string":"#0.00%"},{"name":"Total","dimension":"Sprint","formula":"Aggregate(\n Order(\n Filter(\n [Sprint].[Sprint].Members,\n NOT [Sprint].CurrentMember.getBoolean('Closed')\n AND\n NOT isEmpty([Sprint].CurrentMember.get('Start date'))\n ), \n -- ordering by start date across all boards\n [Sprint].CurrentMember.get('Start date'), BASC\n )\n)","format_string":""},{"name":"Next Future sprints","dimension":"Sprint","formula":"Aggregate(\n Filter(\n [Sprint].[Sprint].Members,\n NOt [Sprint].CurrentMember.getBoolean('Closed')\n and\n [Sprint].CurrentMember.Name <> '(no sprint)'\n and\n isEmpty([Sprint].CurrentMember.get('Start date'))\n AND\n isEmpty([Sprint].CurrentMember.get('Complete date'))\n )\n )","format_string":""},{"name":"Total All cod requirementos","dimension":"Cod Requerimiento","formula":"[Cod Requerimiento].[All Cod Requerimientos]","format_string":""},{"name":"Hiding projects from total","dimension":"Measures","formula":"Case when\n[Cod Requerimiento].currentMember.Level.Name = \"Cod Requerimiento\"\nOR\n(\n[Cod Requerimiento].currentMember.Level.Name <> \"Cod Requerimiento\"\nAND\n[Sprint].Currentmember.level.name <> \"Sprint\"\n)\nthen\n1\nend","format_string":""},{"name":"IEPG Agile","dimension":"Measures","formula":"--annotations.group=PMO Agile\nCASE \nWHEN\n [Cod Requerimiento].CurrentMember.Level.name <> \"Cod Requerimiento\"\nTHEN\n NonZero(\n Avg(\n Filter([Cod Requerimiento].[Cod Requerimiento].Members,\n [Measures].[IEP Proyecto]>0\n ),\n [Measures].[IEP Proyecto]\n )\n)\nELSE\n--Nonzero([Measures].[IEP Proyecto])\n null\nEND","format_string":"#0%"},{"name":"Cod Requerimiento counter","dimension":"Measures","formula":"case when\n[Cod Requerimiento].CurrentMember.level.name <> \"Cod Requerimiento\"\nthen\nNonZero(\nCount(\nFilter(\n[Cod Requerimiento].[Cod Requerimiento].members,\n[Measures].[Issues created]>0\n)\n)\n)\nend","format_string":""}]
}

Thank you in advance!

Regards,

Victor Seger

 

0 answers

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Jul 10, 2018 in Marketplace Apps

If you’re an Atlassian app developer, you’ll want to know about Atlas Camp!

This September 6-7, hundreds of Atlassian App developers will flock to Barcelona Spain to build skills, discover product roadmaps, meet face-to-face with the Atlassian team, and learn how to extend t...

146 views 0 4
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you