Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kalwabharath44
Frequent Visitor

Aggregate Sum and Do an Average

Hi All,

 

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

 

ProjectNameTaskActualStartActualEndDaystoCompleteDepartment
Test 1CMD5/16/2019 5/16/2019 01-Sol
Test 1CMD4/23/2019 4/26/2019 31-Sol
Test 1DM4/29/2019 5/2/2019 32-Por
Test 1DM4/26/2019 5/1/2019 52-Por
Test 1DM5/7/2019 5/10/2019 32-Por
Test 1EF4/2/2019 4/2/2019 33-Test
Test 1FE4/2/2019 4/2/201954-Dep

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

 

ProjectNameTaskActualStartActualEnddaystoCompleteDepartmentAggregatedSumAverage
Test 1CMD5/16/20195/16/201901-Sol33
Test 1CMD4/23/2019 4/26/2019 31-Sol
Test 1DM4/29/2019 5/2/201932-Por1111
Test 1DM4/26/20195/1/201952-Por
Test 1DM5/7/20195/10/201932-Por
Test 1EF4/2/20194/2/2019 33-Test33
Test 1FE4/2/20194/2/201954-Dep33
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

4 REPLIES 4
kalwabharath44
Frequent Visitor

 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
Not applicable

Try this: 

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

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
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors