Average per employee

[T-Pan]

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

[T-Pan]

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 · Accepted solution]

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]

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]

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)

[Dangar332]

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]

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

[VahidDM]

Hey @T-Pan 

Can you post a sample of the expected output?

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/