How to show static parent row data mixed with aggregated columns

[mijoe]

I think I'm trying to do something not fully supported, but was hoping someone can help me figure out a way to do this.  Basically, I'm trying to pull in data for the parent node that's just static data from the parent node while also having a column that sums up its children.

I have 2 tables that look something like this:

ID	Type	Description
1	Apples	Red, Fruit
2	Oranges	Orange, Fruit
3	Pears	Green, Fruit

ID	ParentID	SubType	Number
39	1	Red Delicious	15
40	1	Gala	5
41	2	Sumo	9

What I'm trying to do is put this together in a matrix table and sum the number of each subtype but also maintain the description column of the parent.  Basically, I want to show data about the parent node while also having other columns that do aggregation.  This works if you don't expand any of the rows, but once you expand any of the top level rows, the table goes into "subtotal" mode and any top level item that doesn't have any children shows blank now.  Here's the pre-expand and post-expand example of the matrix I created:

Pre-expand:

Type	Description	SubType	Count
Apples	Red, Fruit	Red Delicious	20
Oranges	Orange, Fruit	Sumo	9
Pears	Green, Fruit

When you expand any of the rows:

Type	Description	SubType	Count
Apples	Red, Fruit	Red Delicious	20
	Red, Fruit (is there a way to only show this data for the parent node?)	Red Delicious	15
	Red, Fruit (is there a way to only show this data for the parent node?)	Gala	5
Oranges	Orange, Fruit	Sumo	9
	Orange, Fruit (is there a way to only show this data for the parent node?)	Sumo	9
Pears	(this is now blank because it doesn't have any children.  want this to keep the "Green, Fruit" description showing)

Any help appreciated!

[v-yangliu-msft · Accepted solution]

Hi  @mijoe ,

When the grouping of information in [SubType] is the same, the matrix will automatically aggregate according to the grouping, can you add a little character to distinguish it.

Here are the steps you can follow：

1. Create calculated table.

True =
var _table1=
SUMMARIZE(
    'Table2',
    'Table1'[ID],'Table2'[ParentID],
    "SubType",
    MAXX(
        FILTER(ALL(Table2),
        [ParentID]=EARLIER('Table2'[ParentID])&&
        'Table2'[ID]=
    MINX(
        FILTER(ALL(Table2),
        [ParentID]=EARLIER('Table2'[ParentID])),[ID])
        ),[SubType])&""&"all",
    "Number",
    SUMX(
        FILTER(ALL(Table2),
    'Table2'[ParentID]=EARLIER('Table2'[ParentID])),
    [Number]))
return
UNION(
    'Table2',_table1)

2. Join the relationships between tables.

3. The matrix Visual settings are as follows.

Row headers – options – Stepped layout – OFF

Row subtotals -- off

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly