Forum Discussion

T-Pan's avatar
T-Pan
Helper I
2 years ago
Solved

Average per employee

I have the following dataset:

Date       EmployeName   Operations

1/1/23.    A.                        15

1/1/23.    B.                         20

1/1/23.    C.                         7

 

For every date, i want to calculate the average operations (sum operations per date divided by the number of employess appears in the same date).

 

 

  • v-zhangti's avatar
    v-zhangti
    2 years ago

    Hi, T-Pan 

     

    You can try the following methods. distinct count resolved by (all activities) is a measure.

    Measure = 
Var _N1=CALCULATE(SUM('Table'[Count Operations]),ALL('Table'))
Var _table=SUMMARIZE('Table','Table'[Day],"Distinct",[distinct count resolved by (all activities )])
Var _N2=SUMX(_table,[Distinct])
RETURN
DIVIDE(_N1,_N2)

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    Hi, T-Pan 

    for new column try below

    just adjust table name

     

    Column = 
var a = 'Table (2)'[date]
var b = SUMX(FILTER('Table (2)','Table (2)'[date]=a),'Table (2)'[operation])
var c = COUNTX(FILTER('Table (2)','Table (2)'[date]=a),'Table (2)'[emp name])
return
DIVIDE(b,c)

     

     

    and for measure try below

    Measure 2 = 
var a = SUMX(FILTER(all('Table (2)'),'Table (2)'[date]=MAX('Table (2)'[date])),'Table (2)'[operation])
var b = COUNTX(FILTER(all('Table (2)'),'Table (2)'[date]=MAX('Table (2)'[date])),'Table (2)'[emp name])
RETURN
DIVIDE(a,b)
    • T-Pan's avatar
      T-Pan
      Helper I
      2 years ago

      This is my table, resolved by column Represents the name of employess, count of operations represents an operations, resolution date is the timestamp where the operation completed.

      The follow image is an table i have created to expalin what i want to measure

      I want to divide the value '17660' by the sum of last column whic represents the distinct names of employess in each date and is the follow measure : (distinct count resolved by (all activities) = distinctcount('ALL ACTIVITIES'[resolved by].))

       

  • T-Pan's avatar
    T-Pan
    Helper I
    2 years ago

    This is my table, resolved by column Represents the name of employess, count of operations represents an operations, resolution date is the timestamp where the operation completed.

    The follow image is an table i have created to expalin what i want to measure

    I want to divide the value '17660' by the sum of last column whic represents the distinct names of employess in each date and is the follow measure : (distinct count resolved by (all activities) = distinctcount('ALL ACTIVITIES'[resolved by].))

     

    • v-zhangti's avatar
      v-zhangti
      Community Support
      2 years ago

      Hi, T-Pan 

       

      You can try the following methods. distinct count resolved by (all activities) is a measure.

      Measure = 
Var _N1=CALCULATE(SUM('Table'[Count Operations]),ALL('Table'))
Var _table=SUMMARIZE('Table','Table'[Day],"Distinct",[distinct count resolved by (all activities )])
Var _N2=SUMX(_table,[Distinct])
RETURN
DIVIDE(_N1,_N2)

      Is this the result you expect? Please see the attached document.

       

      Best Regards,

      Community Support Team _Charlotte

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • T-Pan's avatar
        T-Pan
        Helper I
        2 years ago

        Yes the result is the same with the measure that i create, which is the following: 

        Average Activities per Distinct Analyst per Date =

        DIVIDE(

        SUM('All Activities Table'[Count Operations]),

        SUMX(VALUES('All Activities Table'[Resolution Date Only]),

        CALCULATE(DISTINCTCOUNT('All Activities Table'[Resolved By]))

        )

        )

    • Dangar332's avatar
      Dangar332
      Resident Rockstar
      2 years ago

      Hi, T-Pan 

      try below

      measure =
var a = sumx('ALL ACTIVITIES',[(distinct count resolved by (all activities)])
var b = calculate(sum('ALL ACTIVITIES'[count operation]),removefilters())
return
divide(b,a)