Aggregation of a table based on one column with only one level of hierarchy.

[Nuschnusch]

Hi all,
I could not figure out how to do this.
I have a table with 16 Columns and 5k + rows. In the column x ("ID-Tag") I have multiple different entries which might be identical.
I would like to get a table (or Matrix) where all ID-Tags which are identical are collapsed in on row. This row should be expandable so that all rows that where summarized before are visible, together with the remaining 15 columns.

so for Example (collapsed)

ID-TAGS
Hoover_C

Then upon expanding it should be like

ID TAG           Date              Operator       Equipment       Facility

Hoover_C      17.06.2024     BE_TT            Zentrifuge X    ZZ2

Hoover_C       20.10.2024    ME_RI            Probe 1           ZZ1

Hoover_C       22.10.2024    ME_TT            Probe 3           ZZ1

(Etc.)
Could someone tell me how I can do this?

I tried it with Matrix but then there is a level for each column, so I end up having 15 hierarchical levels to expand...

Thanks so much!

  

[Anonymous · Accepted solution]

Hello, saud968 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@Nuschnusch .I am glad to help you.

like this?

Here's my test data:

ID-Tag	Date	Operator	Equipment	Facility
Hoover_C	17.06.2024	BE_TT	Zentrifuge X	ZZ2
Hoover_C	20.10.2024	ME_RI	Probe 1	ZZ1
Hoover_C	22.10.2024	ME_TT	Probe 3	ZZ1
Alpha_A	15.05.2024	BE_TT	Zentrifuge Y	ZZ3
Alpha_A	18.07.2024	ME_RI	Probe 2	ZZ2

In order to be able to achieve the effect of hierarchy scaling. You need to think about creating columns that act as hierarchies.

Like this.
Creating a calculate column for sorting within a group [son hierarchy].

Subsequently placed in the matrix like this:
rows.
[ID-Tag]
[son hierarchy]

Place the other columns in the values area

If you also want to scale the Tag column it is possible to do so, just create a group to contain all the data (the point is to generate a hierarchy, i.e. to create different groups of categories).

like this:

In addition to using dax to create calculated columns, you can also use power query to create grouped indexes to achieve the same effect.
Here is my M code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\test_11_6.xlsx"), null, true),
    Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID-Tag", type text}, {"Date", type text}, {"Operator", type text}, {"Equipment", type text}, {"Facility", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID-Tag"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Operator", "Equipment", "Facility", "Index"}, {"Date", "Operator", "Equipment", "Facility", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Count",{{"Index", "groupIndex"}})
in
    #"Renamed Columns"

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HCA · Accepted solution]

Hi, Can you please try this,

*Create a Matrix Visual

*Add ID-Tag as Rows

*Add the Remaining Columns as Values

*For the columns where you don’t want aggregation (like Date, Operator, Equipment, etc.), you'll need to adjust the Values field settings:

->Click on each field in the Values well.

->Under the Value field settings, select Don’t summarize. This ensures that the exact values for each ID-Tag are displayed, rather than being aggregated (e.g., summed or averaged).

*Ensure Data Appears Correctly in Expandable Format

->The Matrix visual, by default, will collapse rows by the ID-Tag. The trick is that Power BI automatically allows you to expand/collapse these rows.

->Once you set ID-Tag in the Rows well, Power BI will show each unique ID-Tag as a collapsed row.

->When you click on the plus sign (+) next to a collapsed row, it will expand and show the individual entries corresponding to that ID-Tag.

*Avoid Having Multiple Hierarchical Levels for Each Column

It sounds like you're currently getting 15 hierarchical levels in the rows, which likely happens if you mistakenly drag all the 15 columns into the Rows well. This would create nested rows based on every column, which isn’t what you want.

To fix this, ensure that only ID-Tag is in the Rows section and not the other 15 columns.

Here’s how to structure the visual properly:

->Rows: ID-Tag

->Values: Add the remaining columns like Date, Operator, Equipment, Facility, etc.

This will make sure that ID-Tag values are grouped, and the other columns are displayed as values upon expansion.

*Configure Expand/Collapse Behavior

Once the visual is set up with ID-Tag in the rows, and the other columns as values:

->Collapse All: When the report first loads, the ID-Tag rows will be collapsed.

->Expand/Collapse: You can click the plus sign (+) to expand the rows for each ID-Tag. Clicking the minus sign (-) will collapse the rows again.

If this helps, Please let me know.

[Anonymous · Accepted solution]

Hello, saud968 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@Nuschnusch .I am glad to help you.

like this?

Here's my test data:

ID-Tag	Date	Operator	Equipment	Facility
Hoover_C	17.06.2024	BE_TT	Zentrifuge X	ZZ2
Hoover_C	20.10.2024	ME_RI	Probe 1	ZZ1
Hoover_C	22.10.2024	ME_TT	Probe 3	ZZ1
Alpha_A	15.05.2024	BE_TT	Zentrifuge Y	ZZ3
Alpha_A	18.07.2024	ME_RI	Probe 2	ZZ2

In order to be able to achieve the effect of hierarchy scaling. You need to think about creating columns that act as hierarchies.

Like this.
Creating a calculate column for sorting within a group [son hierarchy].

Subsequently placed in the matrix like this:
rows.
[ID-Tag]
[son hierarchy]

Place the other columns in the values area

If you also want to scale the Tag column it is possible to do so, just create a group to contain all the data (the point is to generate a hierarchy, i.e. to create different groups of categories).

like this:

In addition to using dax to create calculated columns, you can also use power query to create grouped indexes to achieve the same effect.
Here is my M code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\test_11_6.xlsx"), null, true),
    Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID-Tag", type text}, {"Date", type text}, {"Operator", type text}, {"Equipment", type text}, {"Facility", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID-Tag"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Operator", "Equipment", "Facility", "Index"}, {"Date", "Operator", "Equipment", "Facility", "Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Count",{{"Index", "groupIndex"}})
in
    #"Renamed Columns"

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nuschnusch]

Thanks so much, this works!

[Anonymous]

Hi,@Nuschnusch .Thank you for your reply.
You are welcome.

[saud968]

Create Matrix:
Go to Visualizations pane and select Matrix.
Drag ID-Tag to Rows.
Drag Date, Operator, Equipment, Facility, etc., to Values.
Expand/Collapse:
Use the expand/collapse buttons in the matrix to view the detailed rows.


Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Nuschnusch]

Hi Saud
thanks for your reply.
if I do that there is no option to expand/collapse, it just add the columns beside it...

[saud968]

Let’s try a different approach to ensure you get the expandable/collapsible functionality.

In Power BI:
Load Your Data:
Import your data into Power BI.
Create a Matrix Visual:
Add a new Matrix visual to your report.
Drag ID-Tag to the Rows area.
Drag Date, Operator, Equipment, Facility, etc., to the Values area.
Adjust the Matrix Settings:
Rows: Drag ID-Tag to the Rows area.
Columns: Leave this area empty.
Values: Drag Date, Operator, Equipment, Facility, etc., to the Values area.
Enable Stepped Layout:
Click on the matrix visual to select it.
Go to the Format pane (paint roller icon).
Expand the Row headers section.
Turn on the Stepped layout option. This will allow the rows to be grouped and expanded/collapsed.
Expand/Collapse:
You should now see expand/collapse buttons next to the ID-Tag rows. Clicking these buttons will show or hide the detailed rows.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Nuschnusch]

Hi Saud

I followed your suggestion, unfortunatly, there is still no +/- showing in the table and the rows are still showing next to the main row...