Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
al1981
Helper II
Helper II

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

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: 

 

userActivityAverage activity
user 17.426.4
user 26.256.4
user 35.556.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...

14 REPLIES 14
al1981
Helper II
Helper II

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

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

al1981_0-1722580804623.png

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

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

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

 

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

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

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

try this new measure with summarize

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

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

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

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 🙂

Hi @al1981 ,

 

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

 

Best regards,
Community Support Team_ Scott Chang

 

 

v-tianyich-msft
Community Support
Community Support

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.

vtianyichmsft_0-1722563471134.png

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
Super User
Super User

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors