Help with displaying Hierarchy data

[Anonymous]

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 BISample image how hierarchy to be dispalyed

[ChrisMendoza]

@Anonymous,

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

[Anonymous]

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.

[ChrisMendoza · Accepted solution]

@Anonymous,

Your cost center's need to be unique.

I create the following in the query editor:

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.

NodeKey	Expense
1	10
2	20
3	30
4	90
5	80
6	50
7	40
8	10
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.

[Anonymous]

@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.  

[ChrisMendoza]

@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}
    }
)