Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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
Hi,
According to your description, I use the data you provided to test:
Please take following steps:
1)Import into Power BI Desktop and then create a custom column to get its hierarchy level number:
Formula:
=
(Text.Length([Item_name]) - Text.Length(Text.TrimStart([Item_name])))/5+1
And it shows:
2)Add a custom column to build a hierarchy table:
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:
3)After Close&Apply, choose a matrix visual, and it shows:
Here is my test pbix file:
Best Regards,
Giotto Zhi
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:
After Close&Apply, it shows the correct sum result for each level:
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
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.