Aggregate Sum and Do an Average

[kalwabharath44]

Hi All,

I Have a table containing Project Name, Stage , Start and Finish, Days to Complete ,Department

ProjectName	Task	ActualStart	ActualEnd	DaystoComplete	Department
Test 1	CMD	5/16/2019	5/16/2019	0	1-Sol
Test 1	CMD	4/23/2019	4/26/2019	3	1-Sol
Test 1	DM	4/29/2019	5/2/2019	3	2-Por
Test 1	DM	4/26/2019	5/1/2019	5	2-Por
Test 1	DM	5/7/2019	5/10/2019	3	2-Por
Test 1	EF	4/2/2019	4/2/2019	3	3-Test
Test 1	FE	4/2/2019	4/2/2019	5	4-Dep

How can i get an aggregated sum and then do an average as shown below

ProjectName	Task	ActualStart	ActualEnd	daystoComplete	Department	AggregatedSum	Average
Test 1	CMD	5/16/2019	5/16/2019	0	1-Sol	3	3
Test 1	CMD	4/23/2019	4/26/2019	3	1-Sol
Test 1	DM	4/29/2019	5/2/2019	3	2-Por	11	11
Test 1	DM	4/26/2019	5/1/2019	5	2-Por
Test 1	DM	5/7/2019	5/10/2019	3	2-Por
Test 1	EF	4/2/2019	4/2/2019	3	3-Test	3	3
Test 1	FE	4/2/2019	4/2/2019	5	4-Dep	3	3

[kalwabharath44]

 Hi Amit,

The Measure provided sumx(filter(allselected(Table), Table[ProjectName] = max(Table[ProjectName]) && Table[Task] = max(Table[Task])), [DaystoComplete]) did solve my problem

Thankyou for the Help !!!

[Anonymous]

Try this: 

ProjectSummary1 = SUMMARIZE(Projects, Projects[Department], "Avg Time", AVERAGE(Projects[DaysToCompleteCol]))

[Anonymous]

You can create a calculated summarize table summarized on ProjectName, Task and Department.

I am not sure what is the logic/granularity for your "Average" column. If it is Average of "Sum" then it should be at a higher granular level.

[amitchandak · Accepted solution]

@kalwabharath44 , Try two measures like

sumx(filter(allselected(Table), Table[ProjectName] = max(Table[ProjectName]) && Table[Task] = max(Table[Task])), [DaystoComplete])


AverageX(summarize(allselected(Table), Table[ProjectName], Table[Task], "_1", sum(Table[DaystoComplete])),[_1])