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

[al1981]

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

[al1981]

Hi, thanks @aduguid and @v-tianyich-msft  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... 😞

[aduguid]

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

[al1981]

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

[aduguid]

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

[al1981]

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

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

[aduguid]

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

or 

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

[al1981]

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]

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

[aduguid]

try this new measure with summarize

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

[al1981]

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

[aduguid]

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

[al1981]

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 🙂

[v-tianyich-msft]

Hi @al1981 ,

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

Best regards,
Community Support Team_ Scott Chang

[v-tianyich-msft]

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.

[aduguid]

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