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 Everyone-
I'm trying to use the out of the box Matrix visualization and the built in drill down features and I'm stuck on something.
The data has a parent-child relationship in 2 different tables. Each of these tables has a corresponding set of values in a different table (4 tables total). The parent-child is 1:Many, the parent and child tables connected to their values table is also 1:Many.
I would like the data to show the parent value when next to a parent, and the child value when next to a child.
Inside of the Matrix visualization, if I use the value from the parent in the data field, this value is repeated for every child. If I use the value for the child in the data field, it is summed for the parent.
I tried several techniques for calculating the number of child records, but this does not work for the case where there is exactly 1 child.
Does anyone have any thoughts or a solution to this issue?
What I would like - either of these will work
Parent1 Parent1Value
--Child1 Child1Value
--Child2 Child2Value
Parent2 Parent2Value
--Child3 Child3Value
or
Parent1 Parent1Value
--Child1 -blank-
--Child2 -blank-
Parent2 Parent2Value
--Child3 -blank-
What I have working or the results I see in some of the scenarios listed above
Parent1 Parent1Value
--Child1 Child1Value
--Child2 Child2Value
Parent2 Child3Value
--Child3 Child3Value
Using the Parent Value as the "Values" field
Parent1 Parent1Value
--Child1 Parent1Value
--Child2 Parent1Value
Parent2 Parent2Value
--Child3 Parent2Value
Using the Child Value as the "Values" field
Parent1 Sum of (Child1Value and Child2Value)
--Child1 Child1Value
--Child2 Child2Value
Parent2 Sum of (Child3Value)
--Child3 Child3Value
Thanks in advance
Solved! Go to Solution.
Hi @scpm,
I believe that you need something in the line of this article to this article from @marcorusso, in this article the hierarchy are calculated based on the path that each hierarchy level has then based on that we do a lookup of the previous level.
The several levels of hierarchy are calculated in hidden columns then we use to make the final calculations based on the measures we need.
I have made a similar example for another post please check the PBIX file attach this one is making count but measures can be changed to sum or other things.
Check the article in detail and the PBIX file attach with the solution.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @scpm,
I believe that you need something in the line of this article to this article from @marcorusso, in this article the hierarchy are calculated based on the path that each hierarchy level has then based on that we do a lookup of the previous level.
The several levels of hierarchy are calculated in hidden columns then we use to make the final calculations based on the measures we need.
I have made a similar example for another post please check the PBIX file attach this one is making count but measures can be changed to sum or other things.
Check the article in detail and the PBIX file attach with the solution.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks for the quick reply @MFelix .
If I'm understanding the solution correctly, I would need to build a new table that is in the form of:
ID Name ParentID [Other attributes I may want]
The new table combines the Parents and Children into the same table, and leaves the ParentID blank for any of the Parent items. At which point, I can base everything on the Hierarchy pbix from your last message. Or did I misunderstand? Let me give this a try and see how it works. Thanks!
Hi @scpm,
You need to identy for each child theyr parent ID and then everything should work. As a best practice probably the best way is to have a seperate table for this.
If you need any help on this please tell me.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |