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
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