Forum Discussion
Grouping Data in a Table
I'm one of the last Crystal Reports dinosaurs from the 1990s still roaming the earth. I thought I would try PowerBI to create a simple report from an Excel worksheet in which each of the 5 fields below is a separate column:
I'm not getting what I need through a Table or Matrix visual. Do I need to go into Power Query and do the GroupBy stuff, or am I overlooking something obvious?
Thank you,
Thom T.
Hi TTydeman - The layout you're looking for suggests a hierarchical grouping of State → County → Company → City → # Employees. In Power BI, this is best achieved using a Matrix Visual rather than a Table.
you can use Matrix Visual instead of a Table Visual.
In Rows: Add State, then County, then Company, then City (in this order).
Values: Add # Employees (ensure it's set to "Sum" or "Count" depending on your data).Click the drill-down buttons (next to the Matrix visual) to allow hierarchical navigation.
You can also enable the "Stepped Layout" in the Matrix visual's settings to mimic a Crystal Reports-style grouping.
2 Replies
Hi TTydeman - The layout you're looking for suggests a hierarchical grouping of State → County → Company → City → # Employees. In Power BI, this is best achieved using a Matrix Visual rather than a Table.
you can use Matrix Visual instead of a Table Visual.
In Rows: Add State, then County, then Company, then City (in this order).
Values: Add # Employees (ensure it's set to "Sum" or "Count" depending on your data).Click the drill-down buttons (next to the Matrix visual) to allow hierarchical navigation.
You can also enable the "Stepped Layout" in the Matrix visual's settings to mimic a Crystal Reports-style grouping.Thank you - that worked!
