Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
dobregon
Impactful Individual
Impactful Individual

matrix visual with parents/childs

Hi guys,

 

I want to look up to a table visual that i can put some info with parents and childs. Example below is the info that i have

idparentidNameinfo
1NULLTOTAL 1500
21CALIFONIA200
31MEXICO300
41UK300
5NULLTOTAL  25
65CANADA10
7NULLCEZCH 15


As you can see there are some rows with childs and other without child (7). ok, i want to create a matrix table (only 2 levels parent or child) with the + bottom to drill down but the info need to be from each row, not the parent taking the info from their childs (becasue as you see the info is completely different) and the total should be a sum or calculation of the childs not the parents

 

With the typical matrix table that i can do in powerbi i can obtain this (is a pivot table frome xcel but it is valid for the example)

 

In this type i have 2 problems

- czech appears 2 times, when in reality it has only one level so.. we need that without level

- the total values are calculated by the sum of the childs, but i need the info from the total and the grand total should be sum of the childs.

 

I know that there is an option to do creating path, levels and filter the visual in order to solve the first point, but in reality it is very hard to do that and maybe there is a simple way to do this.

do you have any idea?

kind regards!



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
1 ACCEPTED SOLUTION
dobregon
Impactful Individual
Impactful Individual

Finally i have used this way
https://www.daxpatterns.com/parent-child-hierarchies/




Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

6 REPLIES 6
camargos88
Community Champion
Community Champion

Hi @dobregon ,

 

Check this file: Download PBIX 

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



dobregon
Impactful Individual
Impactful Individual

this is similar that i did but it still having the 2 problems

 

- Czehc doesnt appears when it is needed

- The total 1 value should be 500 no tthe sum of the childs, but the total of the matrix should be the sum of the childs or blanks (i have another columns that the total should be blank.



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Hi @dobregon ,

 

I've updated the file, now is working for CEZCH.

 

However for parent 1, there are 3 rows, the total should be 800, no ?

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



dobregon
Impactful Individual
Impactful Individual

thanks for the work @camargos88  but it is not working full.

 

You can see that cezch doesnt have any child, so it need to appear only one level not 2 levels. this is the main problem. 

 

In addition, i know that the total should be 800, but it can have a "manual input" in our platform and can have different number so, we need to show it 

 

for the moment the only way to do that that i know is the following link (that i used in the past)

https://www.thebiccountant.com/2019/10/03/parent-child-hierarchies-with-multiple-parents-in-power-bi...

 

but im asking if someone knows any better solution and for the measure of the second option



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Hi @dobregon ,

 

For CEZCH using matrix it will have a blank parent, it's possible... 

Capture.PNG

For your manual input, you should have any data for it...how do you identify manual input on your data ? 

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



dobregon
Impactful Individual
Impactful Individual

Finally i have used this way
https://www.daxpatterns.com/parent-child-hierarchies/




Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.