Dynamic filtering basing on slicer selection

[DaxNewbie]

Hello, 

I have a dataset including names, titles, tasks, and time worked (see below). I would like to calculate the avg. time worked by task per employee, but also the average time worked by task by the group (indicated by the title) to which that employee belongs. So if an employee is select and she is a partner, the the group average per task would be for only the partners. 

Data:

Avg Per Employee:

Avg Per Title: 

I understand why 'AvgTime' matches 'AvgTimebyTitle'. I just do not know how to instruct Power BI to find the average for all employees whose title is also 'Partner' (the same title as Amy). 

Currently I am using this code: 

AvgTimebyTitle = CALCULATE(AVERAGE(Sheet1[Time]),Filter(Sheet1, Sheet1[Title] = SELECTEDVALUE(Sheet1[Title])))

Any assistance is most appreciated. 

Cheers!

[Greg_Deckler · Accepted solution]

I believe you will need to create a VAR that leverages CALCULATETABLE with an ALL('Data'[Title]). Once you have that, then you can do a GROUPBY with an AVERAGEX across that table to get the average you desire. Something like that. Can you post your data in a form that can be easily copied and pasted?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[DaxNewbie]

Hi @Greg_Deckler: Thank you so much for replying. I believe that I understand the methodology you explained in your post. 

I am also included the data below in a more copy-friendly format. 

Thank you!

Name	Title	Task	Time
Sam	Manager	Research	2
Sam	Manager	Pitch	3
Sam	Manager	Deal Closing	4
Sam	Manager	Admin	8
Sam	Manager	Research	9
Sam	Manager	Pitch	2
Sam	Manager	Deal Closing	1
Sam	Manager	Admin	6
Amy	Partner	Research	7
Amy	Partner	Pitch	8
Amy	Partner	Deal Closing	8
Amy	Partner	Admin	8
Amy	Partner	Research	7
Amy	Partner	Pitch	7
Amy	Partner	Deal Closing	6
Amy	Partner	Admin	6
Michelle	Partner	Research	14
Michelle	Partner	Pitch	15
Michelle	Partner	Deal Closing	13
Michelle	Partner	Admin	12
Michelle	Partner	Research	10
Michelle	Partner	Pitch	11
Michelle	Partner	Deal Closing	12
Michelle	Partner	Admin	13