12. Data Hierarchies and Groups
12.2 Creating a Hierarchy in the Data Pane
Start with a problem
Zoya wants to drill West Maharashtra › Pune › Kothrud in one visual instead of building three.
Explain simply
Create a hierarchy in the Data pane: right-click the top field, then add the lower levels in order.
Show the data
| Region | City | Store |
|---|---|---|
| West Maharashtra | Pune | Kothrud |
| West Maharashtra | Pune | Baner |
| North Maharashtra | Nashik | Gangapur |
| North Maharashtra | Nashik | College Road |
| South Maharashtra | Kolhapur | Rajarampuri |
About this data: This table uses columns: Region, City, Store. This table uses columns: Location, Total Sales (₹). This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv, dark_stores_maharashtra.csv (source: src/practice/).
Demonstrate
- Data pane › expand DarkStore (Orders related on Store, as in 11.2).
-
Right-click Region › Create hierarchy.
Your result should look like this:
DarkStore Region Hierarchy › Region -
Right-click City › Add to hierarchy › Region Hierarchy; then do the same for Store.
Your result should look like this:
Region Hierarchy Region › City › Store -
Right-click the hierarchy › Rename ›
Location. - Model view › select Location › Properties: check the level order; rename levels if needed.
- Matrix: Location on Rows, Total Sales on Values › expand with the + icons.
Show the result
Your result should look like this (fictional sample):
| Location | Total Sales (₹) |
|---|---|
| North Maharashtra | 800 |
| North Maharashtra › Nashik › College Road | 500 |
| North Maharashtra › Nashik › Gangapur | 300 |
| South Maharashtra › Kolhapur › Rajarampuri | 450 |
| West Maharashtra | 850 |
| West Maharashtra › Pune › Kothrud | 650 |
| West Maharashtra › Pune › Baner | 200 |
Explain what happened
The hierarchy is a saved drill path. The matrix shows Region first and opens City and Store under each region when you expand.
Explore further
- Hierarchies are also useful in slicers (12.4) and in the Decomposition tree.
Detailed reference for this lesson:
Steps in Power BI – Location hierarchy
- In Report view or Table view, open the Data pane and expand DarkStore.
- Right-click State › Create hierarchy. A new item State Hierarchy appears with State as its first level.
- Right-click City › Add to hierarchy › State Hierarchy. Repeat for Area and Store Name in this order.
- Right-click State Hierarchy › Rename ›
Location. - To reorder or remove levels, open Model view, select the hierarchy and use the Properties pane. The Hierarchy section lists the levels. Select or deselect columns, drag them into order, then click Apply level changes. You can also right-click a level in the Data pane › Remove from hierarchy.
- Optional: rename levels (right-click a level › Rename), e.g. Store Name → Store.
Repeat for Date: Year › Quarter › Month Name › Date (make sure Month Name is sorted by Month Number, Module 11.5), and for Product: Category › Sub Category › Product Name.
Ravindra Bagale's Tip
Friends, pay attention: putting levels in the wrong order (City above State) makes drill-down meaningless, and a hierarchy can't mix columns from different tables. Also watch out for duplicate names: two "Station Road" areas in different cities show as one bar when the City level is hidden. Use unique store names or keep City visible. Don't make this mistake!
Ravindra Bagale's Tip – मराठी
मित्रांनो, लक्ष द्या: levels चुकीच्या क्रमाने ठेवले (State च्या वर City) तर drill-down ला काही अर्थ राहत नाही, आणि hierarchy मध्ये वेगवेगळ्या tables चे columns मिसळता येत नाहीत. Duplicate नावांकडेही लक्ष ठेवा: City level hide असताना वेगवेगळ्या cities मधले दोन "Station Road" areas एकाच bar मध्ये दिसतात. Unique store names वापरा किंवा City दिसू द्या. ही चूक करू नका!
Ravindra Bagale's Tip – हिंदी
दोस्तों, ध्यान दो: levels गलत क्रम में रखे (State के ऊपर City) तो drill-down का कोई मतलब नहीं रहता, और hierarchy में अलग-अलग tables के columns नहीं मिला सकते. Duplicate नामों पर भी नज़र रखो: City level hide होने पर अलग-अलग cities के दो "Station Road" areas एक ही bar में दिखते हैं. Unique store names इस्तेमाल करो या City को दिखने दो. यह गलती मत करना!
Practice task
Create the Location, Date and Product hierarchies. Hide the separate Area column from report view (right-click › Hide in report view) and check that the hierarchy still works.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| City appears above Region | Levels added in the wrong order | Reorder the levels in Model view |
| Cannot add Orders[Category] to Location | Levels come from different tables | Hierarchy levels must be in one table |
| The matrix shows only regions | Not expanded | Use the +/- icons or Expand all |
Practise independently
Practice task
Expand Pune and Nashik. Which store is the biggest in each city?
Hints
- Kothrud has two orders; so does College Road.
Solution
Pune: Kothrud ₹650 (₹400 + ₹250). Nashik: College Road ₹500 (₹150 + ₹350).
Think like a professional
Name hierarchies for the question they answer (Location, Fiscal calendar) and keep levels from general to specific.
Revision card