Calculating Average

[jabrillo]

I have this table:

Proj	Emp	Dept	Yr and Mo	Alloc Pct
P1	Adam West	HR	January 2024	10%
P1	Adam West	HR	February 2024	10%
P1	John Doe	Sales	January 2024	20%
P1	John Doe	Sales	February 2024	30%
P1	Kelly Shaw	HR	February 2024	50%
P2	Kelly Shaw	HR	March 2024	30%
P2	Kelly Shaw	HR	April 2024	40%

One Dept can have more than one Emp. I tried to calculate the average allocation with:

Calculate(average(Alloc Pct), allexcept(table, table(Proj), table(Emp)).

I need to create a table visual that summarizes the allocation averages by Dept, something like this:

Dept	Emp Count	Proj Count	Alloc
HR	2	2	95%
Sales	1	1	25%

HR has 60% = Adam, 10% avg; Kelly, 50% avg + 35% avg

Sales has 25% = John, 25% avg.

Any help will be appreciated.

[ryan_mayu]

@jabrillo 

you can try to create a new column

alloc = CALCULATE(AVERAGE('Table'[Alloc Pct]),ALLEXCEPT('Table','Table'[Proj],'Table'[Emp],'Table'[Dept]))/CALCULATE(COUNT('Table'[Alloc Pct]),ALLEXCEPT('Table','Table'[Proj],'Table'[Emp],'Table'[Dept]))

then you can display the table visual directly.

pls see the attachment below

[_AAndrade]

You want to calculate the average by emp and Dep and after sum this value by dep?
HR has 60% = Adam, 10% avg +  Kelly, 50% avg !?

[jabrillo]

I want to average by emp and proj, then sum this value by dept.

[_AAndrade]

Try this measure:

The output will be: