Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

How to create report with many level of hierarchy

Dear Everyone

I have a report template that each line item belong to different level in hierarchy.

For example: Total Room Revenue = Hotel Room Revenue + Villa Room Revenue. All line is shown on report as parent and child.

How can i create this report quickly.

Can you do it with my attached file as below link:

https://drive.google.com/open?id=1BHQg2Pcidw5hKBmoqXRODw98YgejOmUv

Thanks and regards

3 REPLIES 3
v-gizhi-msft
Community Support
Community Support

Hi,

 

According to your description, I use the data you provided to test:

11.png

Please take following steps:

1)Import into Power BI Desktop and then create a custom column to get its hierarchy level number:

12.png

Formula:

=

(Text.Length([Item_name]) - Text.Length(Text.TrimStart([Item_name])))/5+1

 

And it shows:

13.png

2)Add a custom column to build a hierarchy table:

14.png

Formula:

=

let s = [Sequence],

t = Table.SelectRows(#"Changed Type1",each [Sequence] <= s and [Level]=1),maxSequence = Table.Max(t,{"Sequence"})[Sequence],

t2 = Table.SelectRows(#"Changed Type1",each [Sequence] >=maxSequence and [Sequence]<=s),

t3 = Table.SelectColumns(t2,{"Item_name","Level"}),

t4 = Table.FromList({1 .. [Level]}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

t5 = Table.AddColumn(t4,"S", each let c = [Column1], maxs = Table.Max( Table.SelectRows(t2,each [Sequence] <= s and [Level]=c),{"Sequence"})[Sequence] in maxs),

t6 = Table.AddColumn(t5,"Item",each let s = [S] in Table.SelectRows(t2,each [Sequence]=s){0}[Item_name]),

t7 = Table.PromoteHeaders(Table.Transpose(Table.RenameColumns(Table.SelectColumns(t6,{"Column1","Item"}),{{"Column1","Item.Level"},{"Item","Item.Level_Name"}})), [PromoteAllScalars=true])

in

t7

 

Then, expand this new table.

And it shows:

15.png

3)After Close&Apply, choose a matrix visual, and it shows:

16.png

Here is my test pbix file:

pbix 

 

Best Regards,

Giotto Zhi

Anonymous
Not applicable

Dear Gizhi

You have changed the order of line item in the report in comparison with original template.

I my file also attached data for present for the report, but it seem that you have forget it.

I also make report like orgininal report but can not use hierarchy. It might be the disadvantage of Power BI when can not set up formular for line item that need to sum other sub line items.

Regards,

Thuan

Hi,

 

According to your problem, I add some data for each lowest level of table in Query Editor:

51.png

After Close&Apply, it shows the correct sum result for each level:

52.png

And for your problem that the new matrix has changed the original table’s order, it is due to Power BI default sorting function which display data sort by alphabet.

 

 

Best Regards,

Giotto Zhi

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.