Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Well, with the data provided, you would simply do:
Measure = AVERAGEX('Table',[Milestone % Complete])
I feel like there is more to this...
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])
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% |
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
22 | |
22 |