Average calculation grouped by project

[Syndicate_Admin]

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.

[Syndicate_Admin]

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.

[lbendlin]

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.

[Syndicate_Admin]

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

[lbendlin]

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-...
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

[Syndicate_Admin]

Hello, I apologize to me, I did not know how to upload a file with the information. Then send link from Wetranfer where I could find a sample of the database along with the expected solution

https://wetransfer.com/downloads/e430e6bd13a17de7f55de50742cfb62320230204231520/bd1286158049dafade5...

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Syndicate_Admin]

Thank you very much, that was what I needed. Just one more question, there is some way to calculate the sum of those averages obtained like this:

That large total can be presented on a card

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark that as Answer.  Write this measure

Average hours 1 = SUMX(VALUES(Data[TASK]),[Average hours])

Hope this helps.

[Syndicate_Admin]

Thank you very much, it worked just as I needed it!