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
Community Support
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.