Get filter metric

Questions & Answers
,
1

Hi Community,

I’m calculating a ‘Utilization’ metric, I want to generate a metric to subtract from the Utilization time the time that people request for leaving request, on my leaving request there is a custom field ‘Number of Days’ where user capture the total days of the leaving request. I want to get this number, multiply it by 8 and subtract it from the ‘Hours spent with sub-task’ value, this is what I tried:
Total Leave request hours:

NonZero(
  Sum(
    Filter(
      Descendants([Logged by].CurrentHierarchy,[Reporter].[User]),
      [Measures].[Issues created] > 0
    ),
    [Measures].[Number of Days created]
  )
)

The idea is to have another calculated measure that goes:
Utilization

[Measures].[Hours spent with sub-tasks] / (160 - [Measure].[Total Leave request hour])

Any ideas on how to show that metric per ‘Logged by’ dimension?

2

Dimension Logged by does not work with issue-level measures like Issues created, Number of Days created. If users themselves report the leave time, then the user dimension Reporter will represent users with reported absent time. However, hours spent with logged by dimension will show what users spent time on issues.

Therefore, you are correct - you would like to use both dimension and combine them in some way. I would suggest using one of the dimensions as primary in the report. For example, Logged by, then you can use a formula to switch from Logged By to Reporter using GetMemberByKey.

Total Leave request hours

Sum(
  DescendantsSet([Logged by].CurrentMember, [Logged by].[User]),
  ([Measures].[Number of Days created],
  [Reporter].[User].GetMemberByKey( -- switch to reporter
    [Logged by].CurrentMember.Key -- by logged by
  ),
  [Logged by].DefaultMember) -- ignore logged by for the measure
)

Then you can check if this measure will work for Utilization%:

[Measures].[Hours spent] / 
Sum(
  DescendantsSet([Logged by].CurrentMember, [Logged by].[User]),
160 -  
 ([Measures].[Number of Days created],
  [Reporter].[User].GetMemberByKey( -- switch to reporter
    [Logged by].CurrentMember.Key -- by logged by
  ),
  [Logged by].DefaultMember) -- ignore logged by for the measure
* 8    -- transform to hours
)

I used the same formula using subtraction from 160h for each user. This will make sure that you get the sum of available hours by users correctly.

I used hours spent instead of hours spent with sub-tasks it should pull in the correct hours for logged by.

Please use only one dimension - Logged by to represent users with those formulas above. See my report example:

image
image1576×892 98.9 KB

See more examples here on how to switch and combine user dimensions:
in demo account

Daina / support@eazybi.com

1 Like
3

Love it!! Thanks Daina.