Sum up 2 custom field values based on 2 category

Questions & Answers
1

Hi Team,

I’m newbie in eazybi, can anyone help me with below requirement

I have 2 number fields one for assignee and one for reporter
but a user can be assignee of some tickets and can be reporter of some tickets, so I want to do calculation(sum up)

  1. I’ll add Issues to rows
  2. mdx should be as follows
    sum = 0
    if(currentuser == user1 as Assignee)
    sum = sum + field1
    else if(currentuser == user1 as reporter)
    sum = sum + field2
    return sum

this needs be for all users who tagged either as assignee or reporter

Thanks & Regards,
Leo

2

Hi @leocfm

Sure thing!

First, you need to import field1 and field2 as measures in the account: Custom field import options

Next, in the report, you can define a new calculated measure in the Measures dimension with a formula like this:

(
  [Measures].[field1 created],
  [Assignee].[User].GetMemberByKey(
    CurrentUser()
  )
)
+
(
  [Measures].[field2 created],
  [Reporter].[User].GetMemberByKey(
    CurrentUser()
  )
)

This formula uses the CurrentUser() function to get the key of the user currently viewing the report and checking if this user is an assignee or a reporter for each issue, if yes, then the numeric values will be returned for these fields.

If you need this for several users, let me know how you intend to portray this in the report. Will the users be in columns or rows, and how would they show up for issues. You can sketch up an example of the end result.

​Best regards,
​Nauris