topic Re: Average per employee in DAX Commands and Tips

Average per employee

T-Pan — Wed, 20 Dec 2023 19:49:25 GMT

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

Re: Average per employee

VahidDM — Thu, 21 Dec 2023 01:24:56 GMT

Hey T-Pan

Can you post a sample of the expected output?

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

Re: Average per employee

Dangar332 — Thu, 21 Dec 2023 06:29:20 GMT

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)

Re: Average per employee

T-Pan — Thu, 21 Dec 2023 12:00:03 GMT

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

Re: Average per employee

T-Pan — Thu, 21 Dec 2023 12:00:42 GMT

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

Re: Average per employee

Dangar332 — Thu, 21 Dec 2023 12:31:26 GMT

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)

Re: Average per employee

v-zhangti — Mon, 25 Dec 2023 11:03:38 GMT

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.

Re: Average per employee

T-Pan — Thu, 28 Dec 2023 22:00:01 GMT

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

)