Hierarchies & Grouping

Hierarchies and grouping help organize data into logical levels, enabling drill-down analysis in reports.

What is a Hierarchy?

A hierarchy is a multi-level structure that allows users to drill down from a general view to a detailed view.

Example Hierarchies: • Date: Year → Quarter → Month → Day • Geography: Country → State → City → Zip Code • Product: Category → Sub-Category → Product Name • Organization: Department → Team → Employee

Creating a Hierarchy in Power BI

Steps:

1. Go to Data View or Report View
2. In the Fields pane, right-click the top-level column (e.g., "Year")
3. Select New Hierarchy
4. Drag additional columns into the hierarchy (Quarter, Month, Day)
5. Rearrange levels by dragging within the hierarchy

Using Hierarchies in Visuals: • Drag the hierarchy to a chart's Axis • Use the drill-down buttons on the visual:

• ⬇️ Drill Down — go one level deeper
• ⬆️ Drill Up — go one level higher
• ↕️ Expand All — show all levels at once

Date Hierarchy (Auto-Generated)

Power BI automatically creates a date hierarchy for any Date column: • Year → Quarter → Month → Day

Disabling Auto Date Hierarchy: • File → Options → Current File → Data Load • Uncheck "Auto Date/Time for new files" • Recommended for better performance and control

Grouping

Grouping combines values within a column into custom groups for simplified analysis.

Grouping in Visuals

Steps:

1. In a visual, select multiple data points (Ctrl + Click)
2. Right-click → Group Data
3. Power BI creates a new group with selected values
4. Remaining values are placed in an "Other" group

Grouping in Fields Pane

Steps:

1. Right-click a column → New Group
2. In the Groups dialog:

• Select values from the "Ungrouped values" list
• Click Group to create a named group
• Repeat for additional groups

1. Choose whether to include an "Other" group
2. Click OK

Binning (Numeric Grouping)

For numeric columns, you can create groups of fixed size (bins).

Steps:

1. Right-click a numeric column → New Group
2. Set Group Type to Bin
3. Set Bin Size (e.g., 10 for groups of 10)
4. Click OK

Example: Age column with Bin Size of 10 → Groups: 0-10, 10-20, 20-30, etc.

Hierarchy vs Grouping