Problems with average between sprints

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.