Hello Team,
The following report is not returning data correctly. It was expected that the IEP Proyecto measure returns the average between IEP Sprint from every sprint inside a “Cod Requirementos” (which is a custom field). But it is calculating the average between all sprints, independently of “Cod Requirementos”.
Here is the formula for IEP Proyecto:
--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
And here is the formula for IEP Sprint (in case that you need it):
--annotations.group=PMO Agile
CASE WHEN [Cod Requerimiento].CurrentMember.Level.Name = 'Cod Requerimiento' THEN
CASE
WHEN [Measures].[Sprint Total Days] <= [Measures].[Sprint Planned Days]
THEN 1
---Sprint Total Days <= Sprint Planned Days + 10%
WHEN [Measures].[Sprint Total Days] <= [Measures].[Sprint Planned Days] * 1.1
THEN 0.95
---Sprint Total Days <= Sprint Planned Days + 15%
WHEN [Measures].[Sprint Total Days] <= [Measures].[Sprint Planned Days] * 1.15
THEN 0.9
---Sprint Total Days <= Sprint Planned Days + 16%
WHEN [Measures].[Sprint Total Days] <= [Measures].[Sprint Planned Days] * 1.16
THEN 0.899
---Sprint Total Days > Sprint Planned Days + 17%
WHEN [Measures].[Sprint Total Days] > [Measures].[Sprint Planned Days] * 1.17
THEN 0.8
ELSE null
END
END
That is a image of a fragment of the report which is presenting the problem:
Just to explain a little better: for the line “Total” in “(none)” inside “IEP Proyecto” column, it was expected to present 95% instead of 86%.
Here is the full report definition, in case that you need some more information:
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Tablero Agile Vice Presidencia",
"folder_name": "Vice-Presidencia",
"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":""}]
}
Hope to hear from you soon.