How to get Project status in a matrix with respect to no of tasks in the table

[Anonymous]

I need help to get a summarized output for each project with respect to its status.

Table

Project Name	Task Name	Planned Start	Planned Finish	Actual Start	Actual Finish	Activity status
ProjectA	Task1	1-Mar-22	2-Mar-22	1-Mar-22	2-Mar-22	Completed
ProjectA	Task2	2-Mar-22	3-Mar-22	2-Mar-22	3-Mar-22	Completed
ProjectB	Task1	4-Mar-22	5-Mar-22	4-Mar-22	5-Mar-22	Completed
ProjectB	Task2	5-Mar-22	30-Mar-22	5-Mar-22		In Progress
ProjectC	Task1	6-Mar-22	11-Mar-22			Defered
ProjectD	Task1	7-Mar-22	20-Mar-22			Not Started
ProjectD	Task2	8-Mar-22	25-Mar-22			Not Started

Output

Project Name	Planned Start	Planned Finish	Actual Start	Actual Finish	Project Status
ProjectA	1-Mar-22	3-Mar-22	1-Mar-22	3-Mar-22	Completed
ProjectB	4-Mar-22	30-Mar-22	4-Mar-22		In Progress
ProjectC	6-Mar-22	11-Mar-22			Defered
ProjectD	7-Mar-22	25-Mar-22			Not Started

[Anonymous]

First Output table required

Project Id	Project Name	Planned Start	Planned Finish	Planned Hours	Actual Start	Actual Finish	Actual Hours	Status	%Complete
6	Project F	11/5/2021	1/11/2022	136	11/5/2021	1/11/2022	198	Completed	100%
23	Project A	2/10/2022	4/28/2022	1241	2/7/2022		629	In Progress	94%
2	Project B	5/2/2022	7/15/2022	340
5	Project E	5/2/2022	6/15/2022	320				Not Started
8	Project G	3/1/2022	5/27/2022	1759	3/1/2022		618	In Progress	54%
10	Project H	3/14/2022	4/20/2022	324	3/17/2022		217	In Progress	63%

[Anonymous]

Project Id	Project Name	WBS	Planned Start	Planned Finish	Planned Hours	Actual Start	Actual Finish	Actual Hours	Status	%Complete
10	Project H	Sub Project 1	3/14/2022	3/18/2022	48	3/17/2022	3/29/2022	78	Completed	100%
		Sub Project 2	3/21/2022	3/25/2022	56	4/15/2022		8	In Progress	21%
		Sub Project 3	3/29/2022	4/1/2022	48	4/11/2022			In Progress	14%
		Sub Project 4	4/4/2022	4/11/2022	108	3/31/2022	4/8/2022	107	Completed	100%
		Sub Project 5	4/13/2022	4/18/2022	16	4/19/2022	4/21/2022	16	Completed	100%
		Sub Project 6	4/18/2022	4/19/2022	16				Not Started	0%
		Sub Project 7	4/19/2022	4/20/2022	16				Not Started	0%
		Sub Project 8	4/19/2022	4/20/2022	16				Not Started	0%

[Anonymous]

I have attached input and output table in the below location.

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

[DataInsights · Accepted solution]

@Anonymous,

Try these measures:

Planned Start = 
CALCULATE ( MIN (Table1[Planned Start] ), ALLEXCEPT ( Table1, Table1[Project Name] ) )

Planned Finish = 
CALCULATE ( MAX (Table1[Planned Finish] ), ALLEXCEPT ( Table1, Table1[Project Name] ) )

Actual Start = 
CALCULATE ( MIN (Table1[Actual Start] ), ALLEXCEPT ( Table1, Table1[Project Name] ) )

Actual Finish = 
VAR vCountBlank =
    CALCULATE (
        COUNTROWS ( Table1 ),
        ALLEXCEPT ( Table1, Table1[Project Name] ),
        ISBLANK ( Table1[Actual Finish] )
    )
VAR vActualFinish =
    CALCULATE (
        MAX ( Table1[Actual Finish] ),
        ALLEXCEPT ( Table1, Table1[Project Name] )
    )
VAR vResult =
    IF ( ISBLANK ( vCountBlank ), vActualFinish, BLANK () )
RETURN
    vResult

Project Status = 
VAR vActualFinish = [Actual Finish]
VAR vTask =
    CALCULATE (
        MAX ( Table1[Task Name] ),
        ALLEXCEPT ( Table1, Table1[Project Name] ),
        Table1[Actual Finish] = vActualFinish
    )
VAR vResult =
    CALCULATE (
        MAX ( Table1[Activity status] ),
        ALLEXCEPT ( Table1, Table1[Project Name] ),
        Table1[Task Name] = vTask
    )
RETURN
    vResult

[Anonymous]

Can some one help me , how to attach a excel to show my detialed table, so that I can get a help on my request.