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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ClaytonP
Regular Visitor

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

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.

5 REPLIES 5
Greg_Deckler
Super User
Super User

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

 

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

 

I feel like there is more to this... 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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 Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.11.1.1Address At Risk CommunitiesAssess 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 Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.21.2.1Address At Risk CommunitiesEnsuring services and information are accessible and suitable to all populations.Publish and distribute full Hurricane Guide in Vietnamese.10%
Emergency Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.21.2.2Address At Risk CommunitiesEnsuring 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 Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.21.2.3Address At Risk CommunitiesEnsuring services and information are accessible and suitable to all populations.Increase distribution of Spanish language materials20%
Emergency Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.31.3.1Address At Risk CommunitiesIdentify 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 Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.31.3.2Address At Risk CommunitiesIdentify 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 Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.31.3.3Address At Risk CommunitiesIdentify and collaborate with partners who support vulnerable populations who are challenged by income, transportation and housing.Identify and schedule presentations  to senior community centers75%
Emergency Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.31.3.4Address At Risk CommunitiesIdentify 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 Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.41.4.1Address At Risk CommunitiesIdentify community based feeding solutions for disasters. Create WebEOC feeding board to track capabilities and operational implementation50%
Emergency Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.41.4.2Address At Risk CommunitiesIdentify community based feeding solutions for disasters. Create Community Feeding Plan25%
Emergency Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community1.41.5.1Address At Risk CommunitiesIncrease 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 co0%
Emergency Management5.1 Maximize partner relationships and public outreach2.12.1.1Engage and Empower
Whole Community Partners
Effectively leverage multi-media and technology platforms to disseminate general and targeted messages.Update EM Marketing Plan0%
Emergency Management5.1 Maximize partner relationships and public outreach2.12.1.2Engage 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 Management5.1 Maximize partner relationships and public outreach2.12.1.3Engage 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 Management5.1 Maximize partner relationships and public outreach2.12.1.4Engage 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 Management5.1 Maximize partner relationships and public outreach2.12.1.5Engage 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 Management5.1 Maximize partner relationships and public outreach2.22.2.1Engage and Empower
Whole Community Partners
Increase business preparedness and participation with emergency management programs.Conduct Beach Business Hurricane Summit50%
Emergency Management5.1 Maximize partner relationships and public outreach2.22.2.2Engage and Empower
Whole Community Partners
Increase business preparedness and participation with emergency management programs.Organize Director-On-Tour with Chambers75%
Emergency Management5.1 Maximize partner relationships and public outreach2.22.2.3Engage and Empower
Whole Community Partners
Increase business preparedness and participation with emergency management programs.Collaborate with TBRPC for small business outreach100%
Emergency Management5.1 Maximize partner relationships and public outreach2.22.2.4Engage and Empower
Whole Community Partners
Increase business preparedness and participation with emergency management programs.Small Lodging Business Continuity Planning Workshops25%
Emergency Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community2.22.2.5Engage and Empower
Whole Community Partners
Increase business preparedness and participation with emergency management programs.Develop ESF 18 Business & Industry  SOG15%
Emergency Management2.1 Provide planning, coordination, prevention, and protective services to ensure a safe and secure community2.22.2.6Engage and Empower
Whole Community Partners
Increase business preparedness and participation with emergency management programs.Launch Business Continuity Pledge to Plan Campaign20%
Emergency Management5.1 Maximize partner relationships and public outreach2.22.2.7Engage and Empower
Whole Community Partners
Increase business preparedness and participation with emergency management programs.Conduct 2nd Business Summit30%

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors