Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Hierarchies


Share

What Is a Hierarchy?

A hierarchy is a set of columns in order, from general to detailed.

A year has quarters, and a quarter has months. A region has stores.

In the First Report chapter, Power BI made a date hierarchy by itself, and you drilled from years down to quarters.

You turned that feature off in the Date Table chapter.

Now you build your own hierarchies.

You create three, one in each dimension table:

TableHierarchyLevels
DateYear HierarchyYear > Quarter > Month
ProductsCategory HierarchyCategory > Product
StoresRegion HierarchyRegion > Store

All the levels of a hierarchy must come from the same table.

That is one more reason why a star schema keeps related columns together: Category is in Products, next to Product, and Region is in Stores, next to Store.


Create the Date Hierarchy

Open poke_mart_model.pbix, the file you built in the Star Schema chapter and extended up to the Date Table chapter, then:

  1. Select Model view.
  2. In the Date table, right-click Year and select Create hierarchy. Power BI adds a hierarchy called Year Hierarchy, with Year as its first level.
  3. With the hierarchy selected, go to the Properties pane. In the Hierarchy section, use the Select a column to add level... list to add Quarter, and then Month, below Year.
  4. Select Apply Level Changes.

Model view with Year Hierarchy selected in the Date table, and the Properties pane showing the hierarchy levels Year, Quarter and Month and the Apply Level Changes button

The picture shows the pane just before you select Apply Level Changes, so the Date table still lists only Year under the hierarchy.

Start with the most general column, and add the levels in order.

Power BI drills down in the same order: from Year to Quarter to Month.

A hierarchy does not move the columns.

Year, Quarter and Month are still in the Date table as normal columns too.

You can also build a hierarchy in the Data pane: right-click the first column and select Create hierarchy, then right-click each next column and select Add to hierarchy.


Create the Product and Store Hierarchies

Repeat the steps for the other two tables:

  1. In the Products table, right-click Category and select Create hierarchy. Add Product as the second level, and apply the changes.
  2. In the Stores table, right-click Region and select Create hierarchy. Add Store as the second level, and apply the changes.

In the Data pane, each table now shows its hierarchy.

Expand a hierarchy to see its levels.

If a hierarchy still shows only its first level, select the hierarchy again to update the list.


Drill Down in a Matrix

A matrix visual is a good place to use a hierarchy.

It works like a PivotTable in Excel.

  1. Select Report view, and click an empty spot on the canvas.
  2. In the Visualizations pane, select Matrix.
  3. In the Data pane, expand Stores and select Region Hierarchy. Both levels go into the Rows well.
  4. Expand Sales and select Quantity. Sum of Quantity goes into the Values well.

The matrix starts at the top level of the hierarchy, so it shows one row per region:

RegionSum of Quantity
Hoenn7,742
Johto9,111
Kanto7,635
Total24,488

Point to the matrix, and the drill icons appear at the top or the bottom of the visual:

IconWhat it does
Drill upGoes back up one level. It is dimmed until you drill down.
Click to turn on drill downTurns on drill mode. Then select one row or data point to see only its next level.
Go to the next level in the hierarchyReplaces the current level with the next level, for all rows.
Expand all down one level in the hierarchyAdds the next level below each row, and keeps the current level.

Select Expand all down one level in the hierarchy.

Each region now shows its stores:

A matrix of Sum of Quantity by Region Hierarchy, expanded to show the two stores under each region, with the drill icons visible on the visual

Region / StoreSum of Quantity
Hoenn7,742
Lilycove City3,724
Slateport City4,018
Johto9,111
Goldenrod City6,194
Violet City2,917
Kanto7,635
Cerulean City4,295
Viridian City3,340
Total24,488

Johto sells the most units, and one store explains most of it: Goldenrod City alone sold 6,194 units, about a quarter of the total.

Now try the other icons:

  • Select Drill up to go back to the three regions.
  • Select Click to turn on drill down, then select Johto. The matrix shows only Johto, with its two stores, Goldenrod City (6,194) and Violet City (2,917), and a Total of 9,111. Select Drill up, and select the drill down icon again to turn drill mode off.
  • Select Go to the next level in the hierarchy. The matrix shows all six stores, without the regions.


Drill Down the Dates

The Year Hierarchy works the same way.

With the matrix selected, remove Region Hierarchy from the Rows well, and select Year Hierarchy from the Date table instead.

The matrix shows the two years.

Select Expand all down one level in the hierarchy to see the quarters:

Year / QuarterSum of Quantity
202411,277
Q12,372
Q22,743
Q33,171
Q42,991
202513,211
Q12,367
Q23,321
Q33,968
Q43,555
Total24,488

Q3 is the busiest quarter in both years.

Expand once more to see the months under each quarter.

They appear in calendar order, Jan to Dec, because you sorted Month by Month Number in the previous chapter.


Try It Yourself

Replace the hierarchy in the matrix with Category Hierarchy from Products, and expand it one level.

You should get these numbers:

Category / ProductSum of Quantity
Balls12,694
Great Ball2,796
Poke Ball9,108
Ultra Ball790
Medicine8,295
Hyper Potion515
Potion5,090
Revive482
Super Potion2,208
Status Healers3,499
Antidote1,799
Awakening722
Paralyze Heal978
Total24,488

Hierarchies work in charts too.

Put a hierarchy on the X-axis of a column chart, and use the same drill icons.

You learn more about these visuals in the Table and Matrix and Bar and Column Charts chapters.


Your Finished Model

Good job! The Poke Mart model is now complete:

  • One fact table, Sales, and three dimension tables: Products, Stores and Date.
  • Three many-to-one relationships, all with Single cross-filter direction.
  • A marked date table, with auto date/time turned off.
  • Three hierarchies to drill through.

So far you have added up Quantity with a simple Sum of Quantity.

In the DAX chapter, you write your own calculations.

For example, a measure called Units Sold does the same as Sum of Quantity, and Total Sales adds up each quantity times its unit price from Products.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.