Forum Discussion
Average calculation grouped by project
Hello, I have a problem to perform a calculation in PowerBi, at the moment I had to do it manually in Excel and I need it to be done in a dynamic way.
The problem is the following tengun a base with the record of hours dedicated to the different stages of a project and I need to get the average time of both each stage, as the sum of those average hours of the projects, but that all this can be visible / filtered according to certain parameters. Next I will try to put the structure of the base together with the expected result
Base:
| Task | Cod Project | Client | Guy | Models | Hours | Role | Size |
| Setup | 001 | NN | Car | 3 | 5.5 | Jr | XL |
| Setup | 001 | NN | Car | 3 | 1 | Sr | XL |
| Delivery | 001 | NN | Car | 3 | 2 | Sr | XL |
| Setup | 002 | SN | Custom | 1 | 1.5 | Sr | S |
| Analysis | 002 | SN | Custom | 1 | 1 | Sr | S |
| Delivery | 002 | SN | Custom | 1 | 2.5 | Jr | S |
| Kick off | 003 | N | Custom | 2 | 0.5 | Jr | M |
Expected result:
So basically is the structure I have of the database and what I need to get to is a table / matrix that first adds the hours dedicated to each task PER PROJECT and then shows me the average of each task. I have already done the test and calculating only the average per Task is not the same as adding the hours of each project and there yes get the average per task:
I appreciate if someone could help me with this since I could not find a solution and as I mentioned before, the idea is that the process works in a more dynamic way when visualizing the information
Michael C.
9 Replies
your expected outcome doesn't seem to match the sample data. Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.Hello, thanks for responding.
Below I send sample of the database:
The expected result would look something like this:
And these avg Time values come out of in this case the sum of the hours per activity per project and then the average of all the projects selected according to the filters, like this:
I hope you can help me and I remain attentive in case additional information is required
Sorry, I cannot work with screenshots. Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
You can solve this in Power BI by creating a measure that first adds up the hours by Task and Project and then averages over those totals. For example:
PromedioPorTask = AVERAGEX( SUMMARIZE( Tabla, Tabla[Cod Proyecto], Tabla[Task], "HorasProyecto", SUM(Tabla[Horas]) ), [HorasProyecto] )This measure will ensure that it is first grouped by project and task, add up the hours, and then take the average, also allowing you to apply dynamic filters according to client, type or any other field.
