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.
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:
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:
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}
}
)