Measure in Jira according to Unique value in custom text and the max value of it

Hi Guys,

I have some challenge that I have been difficulties to solve in a calculated measure.
I have in my Jira Project one custom field that is Text type.
In this custom field you can have any customer name and each issue has another Custom field of number type.

I want to have a calculated measure, that per each unique Custom field of Customer type gives me Max Number on it, and then sum up this max number.

Example:

Issuetype (Customer Type) | Customer Name (BA Name) | Number (Consumption)
1 | Customer1 | 2
2 | Customer2 | 1
3 | Customer1 | 5
4 | Customer3 | 1
5 | Customer2 | 2
Total: 5+2+1 = 8

I use as dimension the Project, since I have multiple projects and I want to calculate by project.
The pseudo code in my head should be something like this:

Sum(
  Filter (Measure.IssueType = "Customer Type" 
  and Measure.Consumption> 0
  and Measure.BA Name is Unique),
Max(Measure.Consumption)
)

My Challenge is what exactly write in the filter set to apply those 3 conditions. The second thing is how to get the unique “BA Name”.

Thank you

You have two challenges here. To get a grouping by Customer name and then for each customer to retrieve the Max consumption value.

I would suggest importing Customer name as a dimension. eazyBI does not support importing text fields as dimensions by default. You would like to add this option to this field explicitly.

You would like to treat it with caution, though. Custom field values with manual data import do not have a limit by length. They could have different data entries (for example, the same value in different cases, for example, ABC, Abc, abc) it might lead to problems analyzing them correctly in eazyBI.

Anyhow, here is a custom field definition example, that overrides default setup by adding import option as a dimension:

   [jira.customfield_NNNNN] 
   data_type = "string" 
   dimension = true

Use the Jira custom field ID instead of NNNNN in the definition above.

Add the updated custom field definition to eazyBI advanced settings or ask Jira administrator or eazyBI administrator to do this for you. eazyBI advanced settings are common for all accounts and only Jira/eazyBI administrators have access to the settings.

Open import options for edit after changes in advanced settings and select the custom field for import as dimension and run an import.

After import, you should be able to use Customer name as a dimension in the report and use it for data analysis.

The next step is calculating a max consumption value for your issues.
You can use Function Max over a set of issues and address a particular custom field. I checked if there is any valu in the field using issue property and then used measure with created date as a parameter for Max function:

-- annotations.total=sum
Max(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateInPeriod(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember
    )
    AND
    [Measures].[Issue type] = "Customer Type"
    AND
    not IsEmpty([Measures].[Issue Consumption])
  ),
  [Measures].[Consumption created]
)

In the formula above, I used annotation to sum values from rows for a total calculation explicitly. The solution should work for version 6.0.1.

Daina / support@eazybi.com

Hi Daina,

Thank you for reaching out!
Few things, First thank you for the tip around custom dimension, I have implemented it, and I think it will be needed even for future reports that I am building.
Currently I have installed the 6.0.1 version of EaziBI.

I have tried to put that formula with some adjustments to my particular business logic and I have now the following formula:

-- annotations.total=sum
Max(
    Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    [Measures].[Consumption resolved]>0
  AND
    [Measures].[Issue type] = "Customer Type"
  ),
  [Measures].[Consumption resolved]
))

I don’t know if I need to adapt the annotations part somehow, but it seems that is the part failing on what I’m expecting to receive.

I have used as dimension: Project (jira Project) and the Customer Name (custom field).
If I expand my table I can see by Customer Name under each Project the Max of the Consumption Value.
I’m just missing the total Consumption of the Max consumption by project.

As an example:
Jira

I would want to see the total Consumption for all projects, then expanding by project I would like to see in each what is the number. In this case I see 2, which is the Max value that I currently. I would expect this 2, would sum up with all the other Max by Customer Type.
Just an ilustrative example. The Sum up shows 2, but I was expecting to count 3, just from the small snapshop.
Jira2

This indicates to me that I’m doing something wrong with annotations, since it seems the only missing part.

Thanks any additional help would be much appreciated.