Sum data using hierarchy and Dynamics 365

[Anonymous]

Hi, I've connected Dynamics 365 into Power BI and need to sum at different levels of the hierarchy. 

In dynamics we use Parent - Child relationships. The child can have an actvity count figure as well as the parent.

I need to do is identify the parent - I beleive I have done this using the Lookupvalue formula.

But I need to be able to get 1 figure showing activity count which is a sum of the count on all children AND the parent. At the moment I can only see how to sum the count on the children or the parent. Not both.

The hierarchy is either 2 or 3 levels high. So will need to look at child - parent - parent and have a figure at the very top entity of all underlying entities.

Has anybody done this before or able to help out?

Thanks

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Assume you have tables below:

Then you could create columns and measures below:

columns:
path = PATH('Account Table'[child id],'Account Table'[parent id])

level = "Level"&" "&PATHLENGTH([path])

Measure = CALCULATE(SUM('Fact Table'[value]),FILTER(ALLSELECTED('Account Table'),FIND(MAX('Account Table'[child id]),[path],1,0)>=1))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Anonymous 

Initially, i think we can use a matrix with many levels in rows to display the visual as expected.

However, without any more information, it is hard to give a clear solution.

Please let me know :

are the "child" and "parent" in the same table?

If they are not in the same table, please show me the relationships among the different tables.

Best Regards

Maggie

[Anonymous]

@v-juanli-msft  yes the child and parent are both the same Account entity.

A child account has a parent account. I've linked the child to the parent using the lookupvalue so I have an extra column in the Account table to show the hierarchy.

I could however duplicate the account table and link the two using Client ID and parentaccount ID as the relationship?