Filter a value from slicer to find the average

[RichardTam]

Lets say I have a staff query:

Staff	Sub type
A	Sales
B	Sales
C	Back Office
D	Back Office
E	Sales

And I have a task table:

Task ID	Created Date	Created by	Finished by
1	1/07/2020	A	B
2	2/07/2020	B	B
3	2/07/2020	A	C
4	4/07/2020	C	A
5	4/07/2020	D	A
6	8/07/2020	B	D
7	8/07/2020	A	A
8	8/07/2020	D	C
9	10/07/2020	E	C
10	12/07/2020	E	D

Each staff creates and finish some tasks. I am now trying to look at reports of each staff
So the report will have a slicer for staff (ie Staff B)
and 2 charts will show me the number of tasks created and finished by B

Right now I am trying to add in an average for the two charts,
so I can see the average of the group, ie how many jobs was created from sales, quick calculation can filter a particular Sub type or Staff, how to make it read whatever from the slicer?

Many thanks

[danielkrol]

Well, since the model of your data is not completely clear to me (are there any relations?) it is a bit a throw in the dark, but I think that creating a measure like this will do...

Finished = 
    CALCULATE( COUNT( Task[Task ID] ),
        FILTER( ALL( Task[Finished by] ),
            Task[Finished by]=SELECTEDVALUE( Staf[Staff] )
        )
    )

Of course, this measure will only get you the number of finished tasks by a staff id. You have to create a measure for started tasks as well, but that is more or less teh same as this slicer, but then with other collum names.

[RichardTam]

I understand the formula, but I am trying to find the average of that staff type.
say the slicer selected "A"
I would like to see the average number of task created by sales

[danielkrol]

Something like this?

avFinished = 
VAR stafCount = CALCULATE( COUNT( Task[Task ID] ),
        FILTER( ALL( Task ),
            Task[Finished by]=SELECTEDVALUE( Staf[Staff] )
        )
    )
VAR totalCount = CALCULATE( COUNT( Task[Task ID] ),
        FILTER( ALL( Task ),
            TRUE()
        )
    )
RETURN DIVIDE(stafCount,totalCount,BLANK())

If not, meaby you can post your pbix file?

[RichardTam]

I have a list of staff to choose in the slicer, they all belong to one of four sub type.
I just want to get an average of the sub type, over each month, compare to the actual performance of that staff

So I would like the total of Sub type 1 to be added into the chart

[amitchandak]

@RichardTam , Created by and  Finished by to staff in staff query. One will active and another one will be inactive. Use userelation to activate other

Refer use relation example here: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...