How to get a SUM of a calculated field which uses number brackets?

Questions & Answers
1

Hello,
We’re in a situation where developers work on multiple products. To find the maximum capacity on a product I want to know the number of developers who have ever worked on and reported hours on that product. Each product is managed as a separate Jira project. A developer is considered 100% trained on a given product if he/she has logged over 500hrs on the corresponding Jira project on any issue types. Training % is prorated if the total hours falls between 500hrs and 100hrs. Less than 100hrs means not trained at all.
Say, developer A has reported 700hrs so far, developer B 750hrs, developer C 250hrs, developer D 100hrs and developer E 50hrs. Out of these 5 developers, A and B are 100% trained, C is 50% trained are D is 20% trained and E is not trained at all. The total I’m looking for is 2.7 or 270% (100+100+50+20+0). Could you help with this calculation?
I could do the calculation part at the individual developer level but when I include the Total column in the report the calculation applies to the total column as well. Here is my logic.

–InnerCalc_Progress
CASE
WHEN [Measures].[Hours spent].Value <= 100 THEN
0
WHEN [Measures].[Hours spent].Value > 100 AND [Measures].[Hours spent].Value <=500 THEN
([Measures].[Hours spent].Value /500)
WHEN [Measures].[Hours spent].Value > 500 THEN
1
ELSE
0
END

–Cross Training Progress
CASE
WHEN
SUM (
{[Time].CurrentHierarchy.Level.Members.Item(0):
[Time].CurrentHierarchyMember},
[Measures].[InnerCalc_Progress]
) >1
THEN 1
ELSE
SUM (
{[Time].CurrentHierarchy.Level.Members.Item(0):
[Time].CurrentHierarchyMember},
[Measures].[InnerCalc_Progress]
)
END

Thanks
Vivek

2

Hi @Vivek,

As you are looking to calculate the total over the users, you should use the Logged by dimension in your calculation to sum the ‘InnerCalc_Progress’. Please try the following:

Sum(
  Filter(
    Descendants([Logged by].CurrentMember, [Logged by].[User]),
    [Measures].[Hours spent] > 0),
  [Measures].[InnerCalc_Progress]
)

Lauma / support@eazybi.com

3

Thanks for the reply. The result of your query is exactly the same as mine. Like I said, I’m able to get the sum correctly at the individual user level but when it’s rolled up to a level, like Total for all users, the logic inside the innerCalc_Progress applies to the total, not to the individual users.

4

Hi @Vivek!

I’m sorry if I have misunderstood you. Could you give me more details about your report - what are you using in the report Rows and Columns, and how do you filter your data?

More information and, preferably, a report example (screenshot and / or report definition would help me better assist you.
You may send this information to support@eazybi.com (mention we discussed it in this thread).

Lauma / support@eazybi.com