Forum Discussion

jabrillo's avatar
jabrillo
Helper I
2 years ago

Calculating Average

I have this table:

ProjEmpDeptYr and MoAlloc Pct
P1Adam WestHRJanuary 202410%
P1Adam WestHRFebruary 202410%
P1John DoeSalesJanuary 202420%
P1John DoeSalesFebruary 202430%
P1Kelly ShawHRFebruary 202450%
P2Kelly ShawHRMarch 202430%
P2Kelly ShawHRApril 202440%

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:

DeptEmp CountProj CountAlloc
HR2295%
Sales1125%

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

Sales has 25% = John, 25% avg.

Any help will be appreciated.

Need Help

4 Replies

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    2 years ago

    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's avatar
      jabrillo
      Helper I
      2 years ago

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

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    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