Matrix Visualization - Changing the Value displayed based on drill down level

[scpm]

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

[MFelix · Accepted 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

[scpm]

Thanks 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!

[MFelix]

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