Hello,
I want to create a report which provides me with a weighted count of epic (based on the LOE set on the epic). Example
LOE=Small than Weighted Epic Count=1
LOE= Medium than Weighted Epic Count=2
LOE=large than Weighted Epic Count=3
I created one measure which calculates the Epic LOE Weight
Aggregate(Filter(
Generate(
ChildrenSet([LOE].CurrentMember),
Descendants([LOE].CurrentMember, [LOE].[LOE])),
[Measures].[Issues resolved] > 0),
CASE [LOE].CurrentMember.Name
WHEN “Small” THEN 1
WHEN “Medium” THEN 2
WHEN “Large” THEN 3
Else 1
END
)
And then created another measure get the ‘Epic Weighted Count (Based on LOE)’
[Measures].[Issues resolved] * [Measures].[CI | Epic LOE Weight][Measures].[CI | Epic LOE Weight]
My issues is that when I aggregate roll-up, it is multiplying the rollups instead of adding them up
Does anyone have any guidence on this?
Hi @Janni_Chohan
If you have imported “LOE” field as dimension, you could try creating a calculated measure using the following formula
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Small]
)
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Medium]
)*2
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Large]
)*3
Martins / eazyBI support
Thank you @martins.vanags. thanks for the feedback, was super helpful. However I had another question. What if in the same calculated member I only wanted to return resolved epics with one ore more issues under the epic? Thoughts on how I might update the MDX syntax?
In that case, you would need to iterate through epics to check their children and that would make the calculation much slower.
Try this formula
NonZero(
SUM(
Filter(
Descendants([Issue.Epic].CurrentHierarchyMember,[Issue.Epic].[Epic]),
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember
)
AND
Count(ChildrenSet(
[Issue.Epic].CurrentMember
))>0
AND
(
[Measures].[Issues resolved],
[Issue type].[Epic]
)>0
),
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Small]
)
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Medium]
)*2
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Large]
)*3
)
)
Martins / eazyBI support
hey @martins.vanags i tried the query and eazyBI doesn’t like it.
@Janni_Chohan
Can you share more details? What exactly do you mean that eazyBI doesn’t like it?
Martins / eazyBI
Hey @martins.vanags here is the syntax I’m using
NonZero(
SUM(
Filter(
Descendants([Issue.Epic].CurrentHierarchyMember,[Issue.Epic].[Epic]),
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember
)
AND
Count(ChildrenSet(
[Issue.Epic].CurrentMember
))>0
AND
(
[Measures].[Issues resolved],
[Issue type].[Epic]
)>0
),
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[(none)]
)
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[1-10]
)
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Extra-Small]
)
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Small]
)
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Medium]
)*2
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Large]
)*3
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[Extra-Large]
)*3
+
(
[Measures].[Issues resolved],
[Issue type].[Epic],
[LOE].[XXL]
)*3
)
)
Below is the output, you can see I am not getting numbers (counts)
Hi @Janni_Chohan
Try saving your calculated measure with “integer” format
Martins / eazyBI team
