Hello everybody,
I am quite new to easybi and not sure if I got everything right by now.
We are estimating work in so called T-Shirt Sizes (S=4 hours, M=16h, L=40h, XL=80h).
Now I want to have a closer look at estimated t-shirt-sizes and spent hours. This should work on all levels of my hierarchy which is like:
-project category
– project
— t-shirt-size
---- parent (<- that’s were t-shirt-size comes from)
----- sub-task (I don’t need it here)
Everything works quite fine, as long as I do not filter Pages. Below is the code for my calculated Member which converts t-shirt-size to hours. Because I couldn’t find a way to correctly sum this up in hierarchy, I added a calculation method for each level:
Case
WHEN [Issue].CurrentHierarchyMember.Level.name = “Category” THEN
SUM([Issue].CurrentHierarchyMember.Children, [Measures].[shirtSizeAsHoursMax])
WHEN [Issue].CurrentHierarchyMember.Level.name = “Project” THEN
SUM([Issue].CurrentHierarchyMember.Children, [Measures].[shirtSizeAsHoursMax])
WHEN [Issue].CurrentHierarchyMember.Level.name = “T-Shirt-Size” THEN
Case
WHEN [Issue].CurrentHierarchyMember.Name = “XXL” THEN
[Issue].CurrentHierarchyMember.Children.Count * 200
WHEN [Issue].CurrentHierarchyMember.Name = “XL” THEN
[Issue].CurrentHierarchyMember.Children.Count * 80
WHEN [Issue].CurrentHierarchyMember.Name = “L” THEN
[Issue].CurrentHierarchyMember.Children.Count * 40
WHEN [Issue].CurrentHierarchyMember.Name = “M” THEN
[Issue].CurrentHierarchyMember.Children.Count * 16
WHEN [Issue].CurrentHierarchyMember.Name = “S” THEN
[Issue].CurrentHierarchyMember.Children.Count * 4
ELSE
0
END
ELSE
CASE
WHEN [Issue].CurrentHierarchyMember.get(‘Shirt Size’) = “S” THEN 4
WHEN [Issue].CurrentHierarchyMember.get(‘Shirt Size’) = “M” THEN 16
WHEN [Issue].CurrentHierarchyMember.get(‘Shirt Size’) = “L” THEN 40
WHEN [Issue].CurrentHierarchyMember.get(‘Shirt Size’) = “XL” THEN 80
WHEN [Issue].CurrentHierarchyMember.get(‘Shirt Size’) = “XXL” THEN 200
ELSE 0
END
END
If I add status to Pages and filter to a dedicated Status (e.g. “Done”), the Sum() for category and project shows still the same value like without a filter.
Two questions arise
- is it possible to sum values in hierarchy in a more easy way
- if not: how to properly filter the cases for category and project properly to all issues with status Done
I am happy to hear from you.
Best Daniel
I found a workaround. Now I use [Measures].[Issues created] instead of
[Issue].CurrentHierarchyMember.Children.Count
which works even for filters, but does not really answer my first question.
Hi @dmorber,
You might want to try another approach by importing Shirt Size also as dimension into eazyBI. This will allow you to group data by Shirt Sizes and then multiply the values by the coefficient of each size group (change of perspective from Issues to Shirt Size).
The formula might look like this:
Sum(
--go throug shirt sizes that are assigned to issues
Filter(
Descendants([Shirt Size].CurrentMember,[Shirt Size].[Shirt Size]),
[Measures].[Issues created] > 0
),
--add coefficient to each size and multiply by issue count assigned to that size.
CASE [Shirt Size].CurrentMember.Name
WHEN "S" THEN [Measures].[Issues created]*4
WHEN "M" THEN [Measures].[Issues created]*16
WHEN "L" THEN [Measures].[Issues created]*40
WHEN "XL" THEN [Measures].[Issues created]*80
WHEN "XXL" THEN [Measures].[Issues created]*200
ELSE 0
END
)
Using this approach, you may use other predefined measures representing issue count. For example, to get the size of resolved issues, use measure “Issues resolved” instead of “Issues created”.
Best,
Zane / support@eazyBI.com
Hello Zane,
thank you for your reply. This approach works almost perfect and it looks better. As you can see, I get some empty values despite a valid Shirt-Size. Any idea, what causes this?
Best,
Daniel
@dmorber it is hard to tell by the fragment of the screenshot. Most likely, the predefined measures used in formulas for “hours (Shirt-Sizes)” and “hours (eazyBI approach)” are different and, therefore, differently related to the dimension used in rows and pages of the report.
Remembers that a measure binds data together and allows you to represent data from different points of view (Dimensions). Therefore, each calculated measure should contain at least one already existing measure. Measure is a treasure. (https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members#Calculatedmeasuresandmembers-Groundrules)
Hello Zane,
I created a better version of this screenshot:
The missing values are on Parent/Issue Level. Right where they are set. As you can see, there are Shirt Sizes set for each Issue, but your approach does not show a calculated value for every issue. I get your point on how to set up measures, but in my understanding it should definitely work on issue level.
I am happy to hear from you.
Best,
Daniel
