Forum Discussion
Hierarchy - Column
Epic = Grand Parent, Task = Parent and Sub-task = child. I need help. I am newbie and trying to create a new column("Total Hours") that will show the sum of Sub-task hours to epic row. See example above in red for your reference.
6 Replies
Hi Sakamoto_S ,
You need to follow a Hierarchy parent-child setup.
Check the SQLBI post about a similar setup.
https://www.daxpatterns.com/parent-child-hierarchies/
Believe that the only question here is the fact that you have the total hours in task and in subtasks.
Hi @
-
Try this,
Total Hours = VAR total_subtask = CALCULATE ( SUM ( 'Table'[Hours] ), FILTER ( ALL ( 'Table' ), 'Table'[Issue-Type] = "Sub-task" ) ) RETURN IF ( SELECTEDVALUE ( 'Table'[Issue-Type] ) = "Epic", total_subtask, BLANK () )Result:
See sample file attached below.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi v-xiaotang, Thanks but what if there are another epics with different issue IDs? for example https://ibb.co/5BgD9D4
Hi Sakamoto_S
-
Thanks for your reply.
But it would be better for the precondition to be given at the beginning. Because the solutions are different.š¤£
-
create the measure firstly:
_IssueID = LEFT(SELECTEDVALUE('Table'[Issue ID]),1)then, create the measure,
Total Hours 2 = VAR total_subtask = CALCULATE ( SUM ( 'Table'[Hours] ), FILTER ( ALL ( 'Table' ), 'Table'[Issue-Type] = "Sub-task" && LEFT ( SELECTEDVALUE ( 'Table'[Issue ID] ), 1 ) = [_IssueID] ) ) RETURN IF ( SELECTEDVALUE ( 'Table'[Issue-Type] ) = "Epic" && LEFT ( SELECTEDVALUE ( 'Table'[Issue ID] ), 1 ) = [_IssueID], total_subtask, BLANK () )Result:
Hope it helps!
See sample file attached below.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solutionāļø to help the other members find it more quickly.
