Adding indentation and hierarchy to a list of project tasks

[ek2112]

I have project tasks in this format. 

P1 is the main task with two sub tasks Summary Task 1 and Summary Task 2. Summary Task 1 in turn has two sub tasks ST-Task 1 and ST-Task 2.  I only have two levels of hierarchy, as indicated by "Indentation Level" column - 0, 1 and 2. I am trying to achieve the following result(s) - group the tasks under each parent task and achieve the following result - and show this with some level of visual indentation (indicated in "Spaced Out Task Name" column) as follows:

I got the spacing worked out as a separate column with CONCATENATE but this is not showing up on the visual. Also not able to group the tasks as shown above. Thank you. Here is the pbix that I am using.

PS: I am using table visual. I tried the matrix visual and adjusted 'row headers' and stepped layout, but did not get the desired result. 

[Phil_Seamark · Accepted solution]

Hi @ek2112

Looks like the HTML is removing the spaces.

Please try this alternative

New Column = 
    REPT(
        REPT(UNICHAR(127),2),
        PATHLENGTH(
            PATH('Tasks'[Task ID],'Tasks'[ParentTaskID])
            )
        ) & 'Tasks'[Task Name]

[alejandram5]

I think I found an easier way using IF, so you just need to add an extra column that indicates if the Account is total,subtotal or detail. and create this column 

New Column =

IF('MS (3)'[Filter] = "Detail",

REPT(" ",9) & 'MS (3)'[Account], IF('MS (3)'[Filter] = "Subtotal",

REPT(" ",6) & 'MS (3)'[Account], 'MS (3)'[Account]))

For my dataset Account will be as I said before, total, subtotal or datail, 

[alejandram5]

I think I found an easier way to do it using IF , so you dont have to add two extra columns, just an extra column that indicates if is a total - subtotal - detail

New Column =

IF('MS (3)'[Filter] = "Detail",

REPT(" ",9) & 'MS (3)'[Account], IF('MS (3)'[Filter] = "Subtotal",

REPT(" ",6) & 'MS (3)'[Account], 'MS (3)'[Account]))

my column account is the name of the account and my  column Filter tolds me if is detail, total or subtotal.

I hope it works for you

[Phil_Seamark]

Hi @ek2112

Please try adding the following calculated column to your model

New Column = 
    REPT(
        REPT(" ",4),
        PATHLENGTH(
            PATH('Tasks'[Task ID],'Tasks'[ParentTaskID])
            )
        ) & 'Tasks'[Task Name]

[ek2112]

Hi @Phil_Seamark

Thanks for looking into this. I have added the column, groups fine, but I am not able to get the indentation on the visual - table or matrix - not sure what I am doing wrong. Here is the updated pbix

[Phil_Seamark · Accepted solution]

Hi @ek2112

Looks like the HTML is removing the spaces.

Please try this alternative

New Column = 
    REPT(
        REPT(UNICHAR(127),2),
        PATHLENGTH(
            PATH('Tasks'[Task ID],'Tasks'[ParentTaskID])
            )
        ) & 'Tasks'[Task Name]

[ek2112]

@Phil_Seamark - got it! It works fine, but now the hierarchy got messed up. Is there any way that the grouping of child tasks are based on the parent task id regardless of their 'task id'? ST-Task 1 and ST-Task 2 should be under Summary Task 1 as their "ParentTaskID" is 1. Thank you. Link to pbix.

[ek2112]

@Phil_Seamark just wanted to clarify that I need to group the child tasks under parent tasks based on parent task ID and display them in the order of parent task ids.

[Phil_Seamark]

You probably need another column that contains something that can be sorted by.

eg. a list like

1

1.1

1.1.1

1.1.2

1.2

1.2.1 

2

2.1

2.1.1

2.1.2

... etc

etc....  

Would that be ok to include on your table visual to use to control sorting?

[Phil_Seamark · Accepted solution]

Adding this calculated column allows you to control the sort

Task ID Sort = SUBSTITUTE(PATH('Tasks'[Task ID],'Tasks'[ParentTaskID]),"|",".")

[Tak94]

Hello,

How would you go about sorting this column if the values go beyond 10? This is what I get as a result.

1

10

10.1

11

11.1

...

2

2.1

However, what I would like is:

1

2

2.1

...

10

10.1

11

11.1

Thank you!!

[ek2112]

@Phil_Seamark - brilliant. This worked like a charm. Thank you. I have to create a new WBS column to get this, but this helps a lot.