Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.