How to count different labels for calculated eazyBI

[Sebastián Delmastro]

I have "reported_by_customer" and "reported_by_inhouse" labels configured on our issues.

I want to calculate an internal KPI = reported_by_customer / (reported_by_customer + reported_by_inhouse) to get an accountable percentage of the total issues reported how many of them were reported by our customers.

This KPI have to be measured on daily basis.

Thanks for the help?

[Janis Plume _eazyBI_ · Accepted solution]

Sebastian,

You can calculate this KPI by using the measure "Issues created count" considering that an issue can have both labels.

To calculate the ratio of issues reported by the customer to the total, you first need to create a calculated member in the Label dimension aggregating both labels ("Customer and Inhouse"):

Aggregate({
 [Label].[reported_by_customer],
 [Label].[reported_by_inhouse]
})

Then the KPI calculation would be:

([Measures].[Issues created count],
 [Label].[reported_by_customer])
/
(
[Measures].[Issues created count],
[Label].[Customer and Inhouse]
)

Best regards,
Janis, EazyBI support

[Sebastián Delmastro]

Thanks @Janis Plume _eazyBI_, that worked great.

Regards

[Julien LANGE]

What would be the KPI calculation if we want to base not on the Volume of Issue but on the Sum of Story points ?

Because we only have the Distinct for 'Issue Created count' but not for 'Issue Story Points'

[Janis Plume _eazyBI_]

Hi,

You are right, there is no standard measure of distinct Story points, but it is possible to make such calculation by a custom measure.

The idea is that we need to iterate over the Issues and sum up the story points for issues matching the report context and the combination of labels:

Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Story points created],
[Label].[reported_by_customer])>0
),
[Measures].[Story points created]
)
/
Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Story points created],
[Label].[Customer and Inhouse])>0
),
[Measures].[Story points created]
)

Note, however, that this solution might have performance problems.

If that is the case, the solution is to pre-calculate the measures during data import into eazyBI by using the Javascript custom fields. 

Please, apply to the support for guidance on how to create such pre-calculated measure in eazyBI.

Kindly,

Janis, eazyBI support

[Mykola Bilenko]

Hello Janis. Indeed, this calculation is very slow and in big tables isn't really an exact science, and, probably, need to create pre-calculated measure on js.

Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Sprint Story Points at closing],
[Label].[asap])>0
),
[Measures].[Sprint Story Points at closing]
)
/
(
Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Sprint Story Points at closing],
[Label].[planned])>0
),
[Measures].[Sprint Story Points at closing]
)
+
Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Sprint Story Points at closing],
[Label].[added])>0
),
[Measures].[Sprint Story Points at closing]
)
+
Sum(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Sprint Story Points at closing],
[Label].[asap])>0
),
[Measures].[Sprint Story Points at closing]
)
)

But how? :-)

[Janis Plume _eazyBI_]

Mykola,

My first suggestion would be trying to avoid the Descendants, which seems possible. in your case. Note that Label is a multi-selection field and eazyBI cannot track changes in such fields. So, the fragments of Sum function in your formula should be equivalent to the tuple like this:

([Measures].[Sprint Story Points at closing],
[Label].[asap])

Anyway, we have had a thread on our community site about how to precalculate dimension from the Label field

https://community.eazybi.com/t/create-dimension-based-on-labels-of-jira-tickets/1534

Kindly,

Janis, eazyBI support

[Mykola Bilenko]

This is more like it. Thanks a lot!