topic Aggregate Sum and Do an Average in DAX Commands and Tips

Aggregate Sum and Do an Average

kalwabharath44 — Thu, 22 Jul 2021 21:00:23 GMT

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

Re: Aggregate Sum and Do an Average

amitchandak — Fri, 23 Jul 2021 06:22:33 GMT

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])

Re: Aggregate Sum and Do an Average

Anonymous — Fri, 23 Jul 2021 12:31:51 GMT

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.

Re: Aggregate Sum and Do an Average

kalwabharath44 — Fri, 23 Jul 2021 15:31:35 GMT

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 !!!

Re: Aggregate Sum and Do an Average

Anonymous — Fri, 23 Jul 2021 15:14:14 GMT

Try this:

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