DAX expression for sub sums

Question

I have a following data set based on VSTS Task, User Story, Feature, Epic hierarchy

Id | Parent ID | Hours completed | Aggregated hours
1  |           |
2  | 1         | 
3  | 2         | 3
4  | 2         | 4
5  | 1         | 
6  | 5         | 2
7  | 5         | 5

I want to calculate for each row what is the aggreagated hours calculated as a sum of all its children. That means, I want to know how many hours have been spent per user story, per feature, per epic.

Id | Parent ID | Hours completed | Aggregated hours
1  |           |                 | 15 (3+4+2+6)
2  | 1         |                 | 7 (3+4)
3  | 2         | 3
4  | 2         | 4
5  | 1         |                 | 8 (2+6)
6  | 5         | 2
7  | 5         | 6

I tried to denormalize the hierachy, so I would have a hierarchy level as a column based on https://www.daxpatterns.com/parent-child-hierarchies/

Id | Parent ID | Level 1 | Level 2 | Hours completed | Aggregated hours
1  |           |         |         |                | 15 (3+4+2+6)
2  | 1         |   1     |         |                | 7 (3+4)
3  | 2         |   1     | 2       | 3
4  | 2         |   1     | 2       | 4
5  | 1         |   1     |         |                | 8 (2+6)
6  | 5         |   1     | 5       | 2
7  | 5         |   1     | 5       | 6

But this got me nowhere... Is there a way to achieve this in Power BI? Tried a SUMX but still far from the correct results

EstCumulativeHoursCompleted = 
    CALCULATE(
        SUMX('Work Items - Today', 'Work Items - Today'[Completed Work]),
        'Work Items - Today'[Parent Work Item Id] = EARLIER('Work Items - 
    Today'[Work Item Id])

)

Thanks for some hints!

P.S. Based on suggestion from Alexis, I mananaged to get this:

Alexis Olson Alexis Olson · Accepted Answer · 2019-01-11T17:02:37

This is the perfect situation to use Parent and Child functions!

You can define a pipe delimited path using the PATH function.

Path = PATH('Work Items'[Id], 'Work Items'[Parent ID])

Adding this to your starting table looks like this:

Id | Parent ID | Hours completed | Path
1  |           |                 | 1
2  | 1         |                 | 1|2
3  | 2         | 3               | 1|2|3
4  | 2         | 4               | 1|2|4
5  | 1         |                 | 1|5
6  | 5         | 2               | 1|5|6
7  | 5         | 6               | 1|5|7

Once you have this, you can sum up the hours for every path that contains the current Id.

Aggregated hours =
CALCULATE (
    SUM ( 'Work Items'[Hours completed] ),
    FILTER (
        'Work Items',
        PATHCONTAINS (
            'Work Items'[Path],             --Path column
            EARLIER ( 'Work Items'[Id] ) )
    )
)

You can also skip the intermediate Path column and just include the path definition within PATHCONTAINS instead of referencing that column.

Aggregated hours =
CALCULATE (
    SUM ( 'Work Items'[Hours completed] ),
    FILTER (
        'Work Items',
        PATHCONTAINS (
            PATH ( 'Work Items'[Id], 'Work Items'[Parent ID] ),  --Path Definition
            EARLIER ( 'Work Items'[Id] )
        )
    )
)

If you keep the Path column, your table should look like this.

Id | Parent ID | Hours completed | Path  | Aggregated hours
1  |           |                 | 1     | 15
2  | 1         |                 | 1|2   |  7
3  | 2         | 3               | 1|2|3 |  3
4  | 2         | 4               | 1|2|4 |  4
5  | 1         |                 | 1|5   |  8
6  | 5         | 2               | 1|5|6 |  2
7  | 5         | 6               | 1|5|7 |  6

If you don't want aggregates for the lowest levels, you can filter out rows where PATHLENGTH is equal to the maximal PATHLENGTH.

DAX expression for sub sums

1 Answers