Single select Drop Down as numbers and then show average instead of sum

Questions & Answers
1

Hi everyone!

I currently have a handful of single select drop down fields with the following options to choose from:

Very good (1)
Good (2)
Satisfying (3)

I would like to use eazyBI to assign numbers (in brackets) to these values and also create the average of said numbers across all issues in a project.
I think i found out how to assign numbers but it adds it up to a sum every time.
How do I assign numbers to the values correctly and get the average shown in the eazyBI report?

Thanks in advance!
Best regards.

2

Hi @Isg,

Welcome to the eazyBI community :tada: !

The most straightforward approach would be to multiply the number of issues for each value with the respective score and then divide it by the number of issues with values. The formula could look similar to the one below:

(([Measures].[Issues created],
[Color].[Green]) * 1
+
([Measures].[Issues created],
[Color].[Red]) * 2
+
([Measures].[Issues created],
[Color].[White]) * 3
)
/
(
([Measures].[Issues created],[Color].DefaultMember)
-
([Measures].[Issues created],[Color].[(none)])
)

In the above example, the Color dimension has three members, “Green”, “Red”, and “White”, which are multiplied by their respective scores 1, 2, and 3. The score sum is then divided by the number of issues with values in that field. Please look at our documentation page for more information on defining calculated measures - ​Calculated measures and members.

Use the Project dimension in the report rows or pages to see results for a particular Project.

Best,
Roberts // support@eazybi.com

1 Like
3

Thank you so much!
This helped me a lot