matrix visual with parents/childs

[dobregon]

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

id	parentid	Name	info
1	NULL	TOTAL 1	500
2	1	CALIFONIA	200
3	1	MEXICO	300
4	1	UK	300
5	NULL	TOTAL  2	5
6	5	CANADA	10
7	NULL	CEZCH	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!

[camargos88]

Hi @dobregon ,

Check this file: Download PBIX 

Ricardo

[dobregon]

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.

[camargos88]

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

[dobregon]

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

[camargos88]

Hi @dobregon ,

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

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

Ricardo

[dobregon · Accepted solution]

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