Hours Spen by Week %

Questions & Answers
1

I need to calculate the percentage for each team by week. I managed to do it, but there’s a nuance: my formula only considers those who have logged their hours, while there are also those who haven’t logged any hours. I need to include them in the percentage calculation as well, because otherwise, the final percentage is incorrect.

Sum(
    [Logged by].[User].Members,
    [Measures].[Hours spent] / 40
) / 
Count(
    Filter(
        [Logged by].[User].Members,
        NOT IsEmpty([Measures].[Hours spent])
    )
)

изображение
In Team1, I have 6 people: 3 of them logged hours (5 days * 8 hours = 40 hours), and 3 of them didn’t log any hours at all. It shows 100%, but it should actually be 50% since half of the team didn’t log any hours. How can I solve this problem?

2

Hi @JiraDeveloperMD

You’re pretty close with the MDX formula!

Please try the following one:

Sum(
  [Logged by].[User].Members,
  [Measures].[Hours spent]
) / 
Count(
  Filter(
    Descendants([Logged by].CurrentMember, [Logged by].[User]),
    (
      [Measures].[Hours spent],
      [Time].CurrentHierarchy.DefaultMember
    ) > 0
  )
) * 40

This will iterate through all of the Logged by Users and check if they have ever had hours spent to associate them with the necessary team and get the correct count of users.

Let me know if this works as expected!
​Best regards,
​Nauris

3

изображение

It doesn’t work as expected, even if / 40.

4

I have another formula, and it works perfectly, but only with people who logged time, while those who didn’t log time are not taken into account, so I can’t calculate the percentage correctly.

Avg(
Filter(
Descendants([Logged by].CurrentHierarchyMember,[Logged by].[User]),
[Measures].[Hours spent] > 0
),
[Measures].[Hours spent] / 40
)
5

Hi @JiraDeveloperMD

The issues seems to be with connecting the Logged by Users with the Logged by Team members.

In the report Rows, the “Logged by Team”, is that a custom dimension imported into eazyBI?

How are you getting the Team data, does each issue has a Team assigned to it or is it done in another way?

​Best regards,
​Nauris

6

The users are imported correctly, as I have another table (click). A person from the team logs hours on different tickets they are working on, but there are some who don’t log time at all on the tickets they are working on. They are shown in that table provided above as an example, but they have no logged hours.