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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Help with displaying Hierarchy data

Hello

 

Any ideas on displaying the hierarchy of data  present in image 1 (excel sheet) in Power BI similar to hierarchy that appears in image 2. 

 

My Data to be used  in Power BIMy Data to be used in Power BISample image how hierarchy to be dispalyedSample image how hierarchy to be dispalyed

 

1 ACCEPTED SOLUTION

@Anonymous,

 

Your cost center's need to be unique.

 

I create the following in the query editor:

1.PNG

 

with this little chunk of code:

 

// This should match the diagram

#table(
type table
[
#"NodeKey" = Int64.Type,
#"CostCenter" = text,
#"ParentKey" = Int64.Type
],
{
{ 1, "CPS", null },
{ 2, "CPS_ADMIN", 1 },
{ 3, "IC", 1 },
{ 4, "ICO", 3 },
{ 5, "PBS", 1 },
{ 6, "PBS_ENG", 7 },
{ 7, "PBS_ENG_F", 5},
{ 8, "PBS_ENG_LA", 5},
{ 9, "PBS_MKT_F", 1}
}
)

// I wrote this before looking at the diagram, only looked at your table
#table( type table [ #"NodeKey" = Int64.Type, #"CostCenter" = text, #"ParentKey" = Int64.Type ], { { 1, "CPS", 1 }, { 2, "CPS_ADMIN", 1 }, { 3, "IC", 1 }, { 4, "ICO", 1 }, { 5, "PBS", 3 }, { 6, "PBS_ENG", null}, { 7, "PBS_ENG_F", 5}, { 8, "PBS_ENG_LA", 5}, { 9, "PBS_MKT_F", 3} } )

You could probably use the text but I the example shows numbers (whole numbers).

 

I then followed the rest of the example at https://www.daxpatterns.com/parent-child-hierarchies/ making all the Calculated Columns and Measures, made up a small 'Expense' table and then visualized in a Matrix:

*edited the picture for the new Query Editor table above.

2.PNG

 

 

NodeKeyExpense
110
220
330
490
580
650
740
810
9 

 

Maybe this example will not work for your current issue but it's possible that some of the concepts might aid you in creating what you hope for.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

5 REPLIES 5
ChrisMendoza
Resident Rockstar
Resident Rockstar

@Anonymous,

 

I used https://www.daxpatterns.com/parent-child-hierarchies/ as a reference for my hierarchy table. Works out well for me.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

The below is the only data I have in source which is just 13 rows all together. However when I try using Path Function, the error I get shows CPS_Admin has multiple values where it is not. Can anyone help me understand how to resolve this error.

Capture1.JPG

 

 

 

@Anonymous,

 

Your cost center's need to be unique.

 

I create the following in the query editor:

1.PNG

 

with this little chunk of code:

 

// This should match the diagram

#table(
type table
[
#"NodeKey" = Int64.Type,
#"CostCenter" = text,
#"ParentKey" = Int64.Type
],
{
{ 1, "CPS", null },
{ 2, "CPS_ADMIN", 1 },
{ 3, "IC", 1 },
{ 4, "ICO", 3 },
{ 5, "PBS", 1 },
{ 6, "PBS_ENG", 7 },
{ 7, "PBS_ENG_F", 5},
{ 8, "PBS_ENG_LA", 5},
{ 9, "PBS_MKT_F", 1}
}
)

// I wrote this before looking at the diagram, only looked at your table
#table( type table [ #"NodeKey" = Int64.Type, #"CostCenter" = text, #"ParentKey" = Int64.Type ], { { 1, "CPS", 1 }, { 2, "CPS_ADMIN", 1 }, { 3, "IC", 1 }, { 4, "ICO", 1 }, { 5, "PBS", 3 }, { 6, "PBS_ENG", null}, { 7, "PBS_ENG_F", 5}, { 8, "PBS_ENG_LA", 5}, { 9, "PBS_MKT_F", 3} } )

You could probably use the text but I the example shows numbers (whole numbers).

 

I then followed the rest of the example at https://www.daxpatterns.com/parent-child-hierarchies/ making all the Calculated Columns and Measures, made up a small 'Expense' table and then visualized in a Matrix:

*edited the picture for the new Query Editor table above.

2.PNG

 

 

NodeKeyExpense
110
220
330
490
580
650
740
810
9 

 

Maybe this example will not work for your current issue but it's possible that some of the concepts might aid you in creating what you hope for.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Anonymous
Not applicable

@ChrisMendoza I highly appreciate your time. I am having some difficulty to replicate waht you did. I am trying to get something similar to the chart you have done with expenses Simple rather than Expenses Amount.  Are you able to share the file or any suggestions where I am wrong. POC.JPG 

@Anonymous,

 

I don't have the ability to share the file. You're probably missing the measures, these were defined in the comments section:

 

*the measure I used for your data is in the spoiler; don't use these ones* 

BrowseDepth:=
ISFILTERED ( Nodes[Level1] )
    + ISFILTERED ( Nodes[Level2] )
    + ISFILTERED ( Nodes[Level3] )

MaxNodeDepth:=MAX ( Nodes[HierarchyDepth] )

Here is all of the Calculated Columns and Measures:

 

Spoiler
/******************START STRUCTURE CALCULATED COLUMNS***********************/

HierarchyPath = PATH(CostCenterStructure[NodeKey],CostCenterStructure[ParentKey])

isLeaf =
CALCULATE(
COUNTROWS(CostCenterStructure),
ALL(CostCenterStructure),
CostCenterStructure[ParentKey] = EARLIER(CostCenterStructure[NodeKey])
) = 0

HierarchyDepth = PATHLENGTH(CostCenterStructure[HierarchyPath])

Level1 =
LOOKUPVALUE(
CostCenterStructure[CostCenter],
CostCenterStructure[NodeKey], PATHITEM(CostCenterStructure[HierarchyPath], 1, 1)
)

Level2 =
IF(
CostCenterStructure[HierarchyDepth] >= 2,
LOOKUPVALUE(
CostCenterStructure[CostCenter],
CostCenterStructure[NodeKey], PATHITEM(CostCenterStructure[HierarchyPath], 2, 1)
),
[Level1]
)

Level3 =
IF(
CostCenterStructure[HierarchyDepth] >= 3,
LOOKUPVALUE(
CostCenterStructure[CostCenter],
CostCenterStructure[NodeKey], PATHITEM(CostCenterStructure[HierarchyPath], 3, 1)
),
[Level2]
)

Level4 =
IF(
CostCenterStructure[HierarchyDepth] >= 4,
LOOKUPVALUE(
CostCenterStructure[CostCenter],
CostCenterStructure[NodeKey], PATHITEM(CostCenterStructure[HierarchyPath], 4, 1)
),
[Level3]
)

/******************END STRUCTURE CALCULATED COLUMNS ***********************/

/******************START EXPENSES MEASURES ***********************/

BrowseDepth =
ISFILTERED(CostCenterStructure[Level1])
+ ISFILTERED(CostCenterStructure[Level2])
+ ISFILTERED(CostCenterStructure[Level3])
+ ISFILTERED(CostCenterStructure[Level4])

MaxNodeDepth = MAX(CostCenterStructure[HierarchyDepth])

Expenses Simple =
IF(
[BrowseDepth] > [MaxNodeDepth],
BLANK(),
SUM(Expenses[Expense])
)

Expenses Amount =
IF(
[BrowseDepth] > [MaxNodeDepth] + 1,
BLANK(),
IF([BrowseDepth] = [MaxNodeDepth] + 1,
IF(
AND(
VALUES(CostCenterStructure[isLeaf]) = FALSE,
SUM(Expenses[Expense]) <> 0
),
SUM(Expenses[Expense]),
BLANK()
),
SUM(Expenses[Expense])
)
)


/******************END EXPENSES MEASURES ***********************/

Here is the Query Editor code to begin the structure:

 

Spoiler
#table(
    type table
    [
        #"NodeKey" = Int64.Type,
        #"CostCenter" = text,
        #"ParentKey" = Int64.Type
    ],
    {
        { 1, "CPS", null },
        { 2, "CPS_ADMIN", 1 },
        { 3, "IC", 1 },
        { 4, "ICO", 3 },
        { 5, "PBS", 1 },
        { 6, "PBS_ENG", 7 },
        { 7, "PBS_ENG_F", 5},
        { 8, "PBS_ENG_LA", 5},
        { 9, "PBS_MKT_F", 1}
    }
)

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.