Tickets reported and resolved by same person


#1

Hello,

I would like to create a monthly report on the percent of the tickets created and resolved by the same user
And the tickets which have the reporter differ from the Assignee .
I would be some thing like in the screenshot :
image

Many thanks in advance for your help.
Regards,
Trang


#2

Hi Trang,

You can use the GetMemberByKey(…) function to find the same member from a different dimension. For example, below would be a formula that would sum tickets where the Reporter is the same user as the Assignee

Sum(
  Descendants([Reporter].CurrentMember, [Reporter].[User]),
CASE WHEN
  [Reporter].CurrentMember.Name = '(anonymous)'
THEN 
NonZero((
  [Assignee].[(unassigned)],
  [Reporter].DefaultMember,
  [Measures].[Issues resolved]
))
ELSE
NonZero((
  [Assignee].[User].GetMemberByKey([Reporter].CurrentMember.Key),
  [Reporter].DefaultMember,
  [Measures].[Issues resolved]
))
END
) / [Measures].[Issues resolved]

I also divided this total number of issues by all Issues resolved to get a Percentage.
Further, you can use the first Measure to subtract from total and get percentage assigned to somebody else, not Reporter.

CASE WHEN [Measures].[Issues resolved] > 0
THEN 
NonZero(1
- [Measures].[% resolved by reporter])
END

Here is an example bar chart with the measures described above

Lauma / support@eazybi.com