Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum data using hierarchy and Dynamics 365

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...
Data Protection
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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?