Project status respect to no of tasks and sub projects in the table

[Anonymous]

Hi

I have attached ,input and required output in the below location. Could someone help me to figure it out how to get overall project status in  a table and also status of subprojects in another table with respect to tasks.

https://www.dropbox.com/scl/fi/tv58upbxs9shu66hcxukc/Sample.xlsx?dl=0&rlkey=pw1vek4klr0fxh4oy4sbnr1l...

[Anonymous]

I have attached one output, if you add status for each sub projects , it shows projects that are 100% complete , also in progress, instead of completed. Could you help on that

[Anonymous]

Hi @Anonymous ,

Try this measure to calculate Status in matrix.

Status (Matrix) =
VAR _STATUS_LIST =
    CALCULATETABLE (
        VALUES ( 'Table'[Status] ),
        ALLEXCEPT ( 'Table', 'Table'[ProjectID], 'Table'[Project Name], 'Table'[WBS] )
    )
VAR _COUNT =
    COUNTAX ( _STATUS_LIST, [Status] )
RETURN
    IF (
        _COUNT = 1,
        IF ( "Completed" IN _STATUS_LIST, "Completed", "Not Started" ),
        "In Progress"
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Firstly, we need to add a [TaskNo] column in the table, we will need it in the calculation below.

Measure:

Latest Actual Finish (Matrix) = 
VAR _STATUS_LIST =
    CALCULATETABLE (
        VALUES ( 'Table'[Status] ),
        ALLEXCEPT ( 'Table', 'Table'[ProjectID], 'Table'[Project Name], 'Table'[WBS] )
    )
VAR _COUNT =
    COUNTAX ( _STATUS_LIST, [Status] )
RETURN
    IF (
        _COUNT = 1
            && "Completed" IN _STATUS_LIST,
        MAX ( 'Table'[Actual Finish] ),
        CALCULATE (
            MAX ( 'Table'[Actual Finish] ),
            FILTER ( 'Table', 'Table'[TaskName] = MAX ( 'Table'[TaskName] ) )
        )
    )

Latest Actual Finish (Table) = 
VAR _MAXDATE = MAX('Table'[Actual Finish])
VAR _STATUS_LIST =
    CALCULATETABLE (
        VALUES ( 'Table'[Status] ),
        ALLEXCEPT ( 'Table', 'Table'[ProjectID])
    )
VAR _COUNT =
    COUNTAX ( _STATUS_LIST, [Status] )
RETURN
IF( _COUNT =1 && "Completed" IN _STATUS_LIST, _MAXDATE,BLANK())

M_Actual Hours = 
VAR _ADDCOLUMN =
    ADDCOLUMNS (
        'Table',
        "Flag",
            VAR _STATUS_LIST =
                CALCULATETABLE (
                    VALUES ( 'Table'[Status] ),
                    ALLEXCEPT ( 'Table', 'Table'[ProjectID], 'Table'[Project Name], 'Table'[WBS] )
                )
            VAR _COUNT =
                COUNTAX ( _STATUS_LIST, [Status] )
            VAR _NEXTTASK =
                CALCULATE (
                    MIN ( 'Table'[TaskNo] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[ProjectID], 'Table'[Project Name], 'Table'[WBS] ),
                        'Table'[TaskNo] > EARLIER ( 'Table'[TaskNo] )
                    )
                )
            VAR _MAXTASK =
                CALCULATE (
                    MAX ( 'Table'[TaskNo] ),
                    ALLEXCEPT ( 'Table', 'Table'[ProjectID], 'Table'[Project Name], 'Table'[WBS] )
                )
            RETURN
                IF (
                    _COUNT = 1
                        && "Completed"
                        IN _STATUS_LIST
                        && 'Table'[TaskNo] = _MAXTASK,
                    1,
                    CALCULATE (
                        MAX ( 'Table'[Actual Start] ),
                        FILTER (
                            ALLEXCEPT ( 'Table', 'Table'[ProjectID], 'Table'[Project Name], 'Table'[WBS] ),
                            'Table'[TaskNo] = _NEXTTASK
                        )
                    )
                )
    )
RETURN
    SUMX ( FILTER ( _ADDCOLUMN, [Flag] <> BLANK () ), [Actual Hours] )

Status (Table) = 
VAR _STATUS_LIST =
    CALCULATETABLE (
        VALUES ( 'Table'[Status] ),
        ALLEXCEPT ( 'Table', 'Table'[ProjectID] )
    )
VAR _COUNT =
    COUNTAX ( _STATUS_LIST, [Status] )
RETURN
    IF (
        "In Progress" IN _STATUS_LIST,
        "In Progress",
        IF ( "Not Started" IN _STATUS_LIST, "Not Started", MAX ( 'Table'[Status] ) )
    )

Then create visuals by sum/avg/min/max function in visual and measures. Result is as below.

Table visual:

Matrix visual:

You can download my sample file to learn more details

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.