topic Re: Calculating average of sales rep activity based on the sume activity of each in DAX Commands and Tips

Calculating average of sales rep activity based on the sume activity of each

al1981 — Thu, 01 Aug 2024 18:29:56 GMT

Dear all

I have table called ContactsFolder, inside i have already calculated values for contacts of customer (if phone call is done from SalesRep to customer, its value 1, if onsite meet, its value 2 etc). Based on that i have calculated monthly average per day (sum of all different activities devided by number of working days (calculated value in calendar table)

Issue i have now is, that i would like to add column in pivot of average of all in this month in order to use it later in graphs (averga line for example)

for example:

user	Activity	Average activity
user 1	7.42	6.4
user 2	6.25	6.4
user 3	5.55	6.4

I wanted to do someting like this

sum[Activity]/counta[user] but its does not work for Activity

If somebody can help me out... If i missed some info, please let me know...

Re: Calculating average of sales rep activity based on the sume activity of each

aduguid — Fri, 02 Aug 2024 01:49:34 GMT

This measure calculates the average activity for each user.

AverageActivityPerUser = AVERAGEX( VALUES(ContactsFolder[User]), CALCULATE(SUM(ContactsFolder[Activity])) )

This measure calculates the overall average activity across all users for the selected month.

OverallAverageActivity = AVERAGEX( VALUES(ContactsFolder[User]), [AverageActivityPerUser] )

Re: Calculating average of sales rep activity based on the sume activity of each

Anonymous — Fri, 02 Aug 2024 01:51:26 GMT

Hi al1981 ,

The average you need is based on the full table, so you need to traverse the full table data. You can check the following results.

Measure = SUMX(ALL('Table'),[Activity])/COUNTAX(ALL('Table'),[user])

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

Re: Calculating average of sales rep activity based on the sume activity of each

al1981 — Fri, 02 Aug 2024 06:26:27 GMT

Hi, thanks aduguid and Anonymous but it looks i am missing some point here...

in both cases, intelisese does not recognize Activity - this activitiy is calculation (sum of many different calucation) in the model, not column in the table... Table was example of pivot table i created based on the model... 😞

Re: Calculating average of sales rep activity based on the sume activity of each

aduguid — Fri, 02 Aug 2024 06:31:47 GMT

Can you paste the DAX? I may be able to troubleshoot it.

Re: Calculating average of sales rep activity based on the sume activity of each

al1981 — Fri, 02 Aug 2024 06:48:40 GMT

And this is Activity example

(ps: ContactsFolder=CallsFolder)

Activity=([Calls Top FF]+[Calls Middle FF]+[Calls Top Phone/2]+[Calls Middle Phone/2])/[UserDays]

and example of Calls Top FF=CALCULATE(DISTINCTCOUNT(CallsFolder[Call: Call Name]),TeamTarget[Team Target]="Top",CallsFolder[Channel]="Face to Face",'Call Type 1'[Call Type 1]="1 to 1 Detailing" || 'Call Type 1'[Call Type 1]="Leaflet only")

Re: Calculating average of sales rep activity based on the sume activity of each

aduguid — Fri, 02 Aug 2024 07:12:56 GMT

Since [Activity] is a measure it's already being aggregated. It may work if you remove the "CALCULATE(SUM(" with the closing "))"

Re: Calculating average of sales rep activity based on the sume activity of each

al1981 — Fri, 02 Aug 2024 07:26:24 GMT

Like this? as it returns me same avergare per user...

=AVERAGEX(
VALUES(CallsFolder[Owner Name]), (CallsFolder[Activity]))

Re: Calculating average of sales rep activity based on the sume activity of each

aduguid — Fri, 02 Aug 2024 07:36:54 GMT

Sorry, for the AVERAGEX it may be better doing a calculated column for the [Activity]. Then you would be able to average per user

=AVERAGEX(VALUES(CallsFolder[Owner Name]), SUM('CallsFolder'[Activity_Calculated_Column]))

AverageActivityPerUser = AVERAGEX( SUMMARIZE( CallsFolder, CallsFolder[Owner Name], "UserActivity", [Activity] ), [UserActivity] )

Re: Calculating average of sales rep activity based on the sume activity of each

al1981 — Fri, 02 Aug 2024 07:44:03 GMT

it could be i am lost 🙂 but creating calculated column for activity... I am not sure i get this... Acitivity is changing every month. I have dashboard with slicer and based on the month, activity is totally different... so really not sure how to change this from measure to calucated column... It might also significantly slow down model... in callsfolder i have now app 150.000 rows and this is only for 2 years now...?

Activity=([Calls Top FF]+[Calls Middle FF]+[Calls Top Phone/2]+[Calls Middle Phone/2])/[UserDays]

Re: Calculating average of sales rep activity based on the sume activity of each

aduguid — Fri, 02 Aug 2024 07:46:19 GMT

try this new measure with summarize

AverageActivityPerUser = AVERAGEX( SUMMARIZE( CallsFolder, CallsFolder[Owner Name], "UserActivity", [Activity] ), [UserActivity] )

Re: Calculating average of sales rep activity based on the sume activity of each

al1981 — Fri, 02 Aug 2024 08:14:32 GMT

hm...I get same result as "Activity" - average activity per month... clearly i missed something... I will need to re-check my model...

Re: Calculating average of sales rep activity based on the sume activity of each

aduguid — Fri, 02 Aug 2024 08:16:44 GMT

If you post some example data, I can try to setup the measure on my end.

Re: Calculating average of sales rep activity based on the sume activity of each

al1981 — Fri, 02 Aug 2024 08:29:07 GMT

Really appreciate your willingness, but i would need to post really a lot of data and cant anomize them so easily... Honestly - i already looked on the net, and all solutions were so simple and i felt... strange as i could not solve it by myself 🙂 but now i see: either its my model (most probably) or ... No idea 🙂

Re: Calculating average of sales rep activity based on the sume activity of each

Anonymous — Fri, 16 Aug 2024 06:10:06 GMT

Hi al1981 ,

Can you share the sample pbix file to help you better?

Best regards,
Community Support Team_ Scott Chang