Average of Other Users

[usmanrafiq]

Hello,

I have table which have user ID projectID  they worked on  and Time Spent

like this (sample data)

UserID	Project ID	Time Spent
1	1	5
2	1	3
3	1	5
4	1	4

I have created a report which have users ID as page filter 

How to create a clustered bar chart which shows that specific user ID spent time in one bar and shows AVG(other users spent time) in another bar 

[ryan_mayu · Accepted solution]

@usmanrafiq 

You can create a measure to calculate other users' average spend time.

Measure = 
VAR selecteduser = SELECTEDVALUE(user[UserID ])
return AVERAGEX(FILTER(all(user),user[UserID ]<>selecteduser),user[Time Spent])

[usmanrafiq]

Can i also use it by Projects

like 

USer 1 Project 1 Hours  VS  Other Users Project 1 Hours (Avg)

USer 1 Project 2 Hours  VS  Other Users Project 2 (AVG)

[ryan_mayu]

@usmanrafiq 

Maybe something like below. you can have a try.

Measure = 
VAR selecteduser = SELECTEDVALUE(user[UserID ])
VAR selectedproject = SELECTEDVALUE(user[projectID])
return AVERAGEX(FILTER(all(user),user[UserID ]<>selecteduser && user[projectID]<>selected project),user[Time Spent])

[amitchandak]

@usmanrafiq , You have created a clustered line chart where for line you use a measure like this

Avg = Average(allselected(Table[user]),[Time Spent])

better to a seprate user table

Avg = Average(all(User[user]),[Time Spent])

[usmanrafiq]

can you provide your power bi file so that i can look at the example