Effort average based on a subset of issues

The custom field “Provider ACME” holds the number of hours this specific provider spent working on an issue. But this provider does not work on ALL my issues. For those issues where he doesn’t work, the “Provider ACME” custom field value is zero.
So, considering an issue creation start date (that is selectable by the Time Dimension of eazyBI), I need to get the average time spent by this Provider working on my tasks since then as in this formula:
Average = (Total of hours spent by the provider since the selected date) / (total of issues created since the selected date where “Provider ACME” custom field is different from zero).
For each provider we have, there is a different custom field that is used to register the hours worked by this provider. So, we have “Provider ACME”, “Provider XPTO”, “Provider GIZMO” and so on.
I appreciate if you can help me creating this measure that will be used with each provider.
Thanks a lot.



The following calculation gives the count of issues having the numeric value of a custom field:

    DateInPeriod([Measures].[Issue created date],
    [Time].CurrentHierarchyMember) AND
    [Measures].[.... created]>0

You can use this formula as the denominator in the ratio to calculate the average value of the numeric custom field for the issues in your report for the period.

Janis, eazyBI support

Thanks for the clues, Janis!