Forum Discussion
Help with displaying Hierarchy data
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.
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.
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.
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.
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}
}
)