Get the average of the dropdown options

Questions & Answers
, , ,
1

Hello,

I have two dropdown fields in Jira, called ‘Score’ and ‘CSAT’. It contains number options such as 1, 2, 3, 4, 5.
All my Jira tickets have these two fields on them. Now, I need to find the average value of ‘Score’ and ‘CSAT’ in EazyBI. How can I do that?
Please see the screenshot for what I’m looking for.

Screenshot 2023-04-02 at 6.23.56 PM
Screenshot 2023-04-02 at 6.23.56 PM1078×568 14.3 KB

Thanks,
Simar

2

Hi,

You can obtain some of the more simple calculated results from your measures pretty easily. For any measures in your report, left-click on the header and you will get a contextual menu, amongst which you will find the option “Add calculated” as seen in my screenshot.

image
image732×385 19.7 KB

3

Hi @Simarpreet,

A warm welcome to the eazyBI community!

Select list fields are recognized as “strings” in eazyBI by default. Thus, they can be imported as dimensions and issue properties out of the box. For such an arrangement, you can define a new calculated measure that divides the total of the field values by the number of issues with the field values. The formula could look similar to the one below for the field “Score”:

-- numeric value sum of all Scores
Sum(
  Filter(
    [Score].[Score].Members,
    [Score].CurrentMember.Name <> '(none)'
  ),
  ([Measures].[Issues created] * Cast([Score].CurrentMember.Name as numeric))
)
/
-- number of issues with Score
Sum(
  Filter(
    [Score].[Score].Members,
    [Score].CurrentMember.Name <> '(none)'
  ),
  [Measures].[Issues created]
)

Try to define a similar calculated measure formula for the other field. Please look at our documentation page for more information on defining calculated measures - ​Calculated measures and members.

Another approach is changing the field “Score” and “CSAT” data types to integer and importing the values as measures. Because of the field type and how the values are saved in the Jira issue JSON, it is not a simple data type change, and custom JavaScript code must be involved. Let me know if you want to explore this option. For more details, visit the JavaScript calculated custom fields documentation page - JavaScript calculated custom fields.

Best,
Roberts // support@eazybi.com