Need to group items by one column and then show average based on data from another column

[ClaytonP]

Newbie here and am really struggling with this.

I have dataset (SharePoint list) with work plan in 1 column, and milestone % complete in another column.

I can get the groupby done ok but can not figure out how, once I have groupby done to also get the milesone average % complete figured.

For example:

Workplan

1.1 = 20%

1.2 = 25%

1.3 = 0%

1.4 = 35%

Average % Complete = 20+25+0+35=80 / 4 = 20%

Anyhelp would be great folks.

[Greg_Deckler]

Well, with the data provided, you would simply do:

Measure = AVERAGEX('Table',[Milestone % Complete])

I feel like there is more to this... 

[ClaytonP]

Thanks, I have got that far, I prob. should have explained better.

How can I combine the 2 so that when I do the groupby, am also able to do the average.

I can supply small sample of data or ,pbix file if that would help.

[Greg_Deckler]

Yes, sample data or sample PBIX is always appreciated. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

So, if I understand correctly, you want to create a table variable with your GROUPBY statement. Then you take your AVERAGEX across that. Like:

Measure = 

  VAR __Table = GROUPBY('Table',[Column],"Number",SUMX(CURRENTGROUP(),[Some other column]))

RETURN

  AVERAGEX(__Table,[Some other column])

[ClaytonP]

Greg

Sorry for the delay, been a bit hetic at work.

How can I get you my pbix file and data?

I dont see add attachment anywhere.

yes you are correct I want to take the workplans and basically roll them up to an avg. by plan after they have been grouped.

As you will see I can group them just cant get thew avg. to work correctly.

Here is some data at least below.

DeptName StrategicPlanGoal Workplan Tracking Milestone Tracking Department Goal Inititative Milestone Milestone Percent Complete

Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.1	1.1.1	Address At Risk Communities	Assess and improve communication capabilities with deaf or hard of hearing, blind or low vision.	Develop strategies that incorporate ASL interpreters for EM videos and promotion of YouTube to blind and low vision individuals.	20%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.2	1.2.1	Address At Risk Communities	Ensuring services and information are accessible and suitable to all populations.	Publish and distribute full Hurricane Guide in Vietnamese.	10%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.2	1.2.2	Address At Risk Communities	Ensuring services and information are accessible and suitable to all populations.	Distribute emergency information through faith-based communities that serve non-English speaking residents.	25%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.2	1.2.3	Address At Risk Communities	Ensuring services and information are accessible and suitable to all populations.	Increase distribution of Spanish language materials	20%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.3	1.3.1	Address At Risk Communities	Identify and collaborate with partners who support vulnerable populations who are challenged by income, transportation and housing.	Create distribution plan for homeless outreach materials.	10%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.3	1.3.2	Address At Risk Communities	Identify and collaborate with partners who support vulnerable populations who are challenged by income, transportation and housing.	Participation in projects with Area Agency on Aging,  Duke, AARP, Neighborly Senior Services.	50%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.3	1.3.3	Address At Risk Communities	Identify and collaborate with partners who support vulnerable populations who are challenged by income, transportation and housing.	Identify and schedule presentations  to senior community centers	75%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.3	1.3.4	Address At Risk Communities	Identify and collaborate with partners who support vulnerable populations who are challenged by income, transportation and housing.	Promote PSTA transportation assistance and assist sign-ups to special needs shelters and transportation assistance.	25%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.4	1.4.1	Address At Risk Communities	Identify community based feeding solutions for disasters.	Create WebEOC feeding board to track capabilities and operational implementation	50%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.4	1.4.2	Address At Risk Communities	Identify community based feeding solutions for disasters.	Create Community Feeding Plan	25%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	1.4	1.5.1	Address At Risk Communities	Increase involvement with community stakeholders to assist vulnerable populations.	Institute Phase 2 of the Mobile Home Park Safety Campaign, work with partners to expand Storm Kit Project, increase presentations of outreach to vulnerable populations in partnership with Red Cross, FDs, CERTs, USCG Auxiliary, Animal Services and other co	0%
Emergency Management	5.1 Maximize partner relationships and public outreach	2.1	2.1.1	Engage and Empower Whole Community Partners	Effectively leverage multi-media and technology platforms to disseminate general and targeted messages.	Update EM Marketing Plan	0%
Emergency Management	5.1 Maximize partner relationships and public outreach	2.1	2.1.2	Engage and Empower Whole Community Partners	Effectively leverage multi-media and technology platforms to disseminate general and targeted messages.	Build base collection of videos/podcasts appropriate to before, during or after a storm to post on websites, social media, Constant Contact, TV and radio.	0%
Emergency Management	5.1 Maximize partner relationships and public outreach	2.1	2.1.3	Engage and Empower Whole Community Partners	Engage educational partners and students to foster preparedness and awareness of all hazards.	Target middle school ages through Build a Storm Kit contest to help at-risk community stock up for Hurricane Season.	0%
Emergency Management	5.1 Maximize partner relationships and public outreach	2.1	2.1.4	Engage and Empower Whole Community Partners	Engage educational partners and students to foster preparedness and awareness of all hazards.	Target elementary school ages through Ready Kits summer programs at community centers.	50%
Emergency Management	5.1 Maximize partner relationships and public outreach	2.1	2.1.5	Engage and Empower Whole Community Partners	Engage educational partners and students to foster preparedness and awareness of all hazards.	Target high school ages through PPHS Advisory Board involvement, Youth Ambassador Program, Day at PSC, Disaster Day.	50%
Emergency Management	5.1 Maximize partner relationships and public outreach	2.2	2.2.1	Engage and Empower Whole Community Partners	Increase business preparedness and participation with emergency management programs.	Conduct Beach Business Hurricane Summit	50%
Emergency Management	5.1 Maximize partner relationships and public outreach	2.2	2.2.2	Engage and Empower Whole Community Partners	Increase business preparedness and participation with emergency management programs.	Organize Director-On-Tour with Chambers	75%
Emergency Management	5.1 Maximize partner relationships and public outreach	2.2	2.2.3	Engage and Empower Whole Community Partners	Increase business preparedness and participation with emergency management programs.	Collaborate with TBRPC for small business outreach	100%
Emergency Management	5.1 Maximize partner relationships and public outreach	2.2	2.2.4	Engage and Empower Whole Community Partners	Increase business preparedness and participation with emergency management programs.	Small Lodging Business Continuity Planning Workshops	25%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	2.2	2.2.5	Engage and Empower Whole Community Partners	Increase business preparedness and participation with emergency management programs.	Develop ESF 18 Business & Industry  SOG	15%
Emergency Management	2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community	2.2	2.2.6	Engage and Empower Whole Community Partners	Increase business preparedness and participation with emergency management programs.	Launch Business Continuity Pledge to Plan Campaign	20%
Emergency Management	5.1 Maximize partner relationships and public outreach	2.2	2.2.7	Engage and Empower Whole Community Partners	Increase business preparedness and participation with emergency management programs.	Conduct 2nd Business Summit	30%

[ClaytonP]

exactly!

I have workplans that range from a single workplan

to workplans that may have up to say 7 parts or milestones

I can group all of them correctly but I now want to get the average of the workplans.

1.1 has 1 milestone at 20% so it would show 20% if selected

1.2 has 3 milestones at 25%, 50%, 75% and 0% so it would show 37.5% if selected

I dont really need a total of all the workplans just when they are grouped

I can get them to sorta work but run into issues when I try to combine them into one expression.

By the way thank you for your help so far as I haven't been able to fully grasp the concept as of yet.