Average of calculated measure

[clement_dd_64]

Hello,
i have performed a specific calculated measure by this formula (called “ratio_tps” in my project):
Int((([Measures].[Original estimated hours]-[Measures].[Hours spent])/[Measures].[Original estimated hours])*100)

This measure is used in a report where i have set “project”, “time” and" issue" as Rows.
“Time” and “project” are also set as page.

I would like to get the averages of the “ratio_tps” calculated by month!
with this formula i have no correct result… why? how can i do a such calculating?

Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentMember.GetDate('Created at'),
      [Time].CurrentHierarchyMember
    )
  ),
  (
    [Measures].[ratio_tps],
    [Time].CurrentHierarchy.DefaultMember
  )
)

best regards

[gerda.grantina]

Hi @clement_dd_64 ,
You can select additional options to import additional measures for time tracking.
One of the imported measures is “Original estimated hours accuracy %” that calculates a similar ratio you are checking in your formula, but based on the resolution date as then the work is completly done and the hours spent won’t grove thus the ratio is correctly calculated (usually then it won’t change as hours are not logged on resolved issues).

But if you want to create a custom calculation based on issue creation date, first I would suggest changing your original ratio_tps formula to this:

CASE WHEN [Measures].[Original estimated hours]>0
THEN
CoalesceEmpty(([Measures].[Original estimated hours]-[Measures].[Hours spent]),0)/
[Measures].[Original estimated hours]
END

This will avoid dividing by zero and you need to select formatting ##% integer percentage:

best,
Gerda // support@eazybi.com

[clement_dd_64]

Thank you very much this was what i need! (Coalescempty)