Compare your score against your team/ Department/ Org etc

[rax99]

Sorry guys Im a bit of a newb to this PBI stuff. Havent seen this question answered in this specific context.

So here are a few rows from the dummy Table1:

SalesID	Date	AgentId	Primary_Skill	Sub_Skill	Quality	Satisfaction	Perm Rev	Temp Rev	TeamID	DeptID	Org
1	1526848620	1	ICR	Comm	50	7	33.68	69.15	T3	D2	WM
2	1526384177	2	ICR	Comm	5	2	84.30	28.82	T2	D1	WM
3	1526021674	3	ICR	Resi	21	7	44.63	35.43	T3	D2	WM
4	1526110988	4	ICR	Resi	59	7	50.03	9.41	T3	D2	WM
5	1526353695	5	ICR	Comm	62	9	50.84	12.36	T1	D1	WM
6	1527599316	6	ICR	Comm	88	6	30.80	45.97	T4	D1	WM
7	1525586752	7	ICR	Comm	14	9	53.08	84.90	T1	D1	WM
8	1527552156	8	ICR	Resi	24	9	80.80	69.96	T2	D2	WM
9	1525499326	9	ICR	Resi	56	9	85.00	3.58	T3	D2	WM
10	1525660804	10	ICR	Resi	41	4	28.36	25.45	T3	D1	WM
11	1526524152	11	ICR	Resi	10	5	33.91	69.45	T4	D1	WM
12	1526424737	12	ICR	Resi	18	4	7.90	47.24	T3	D1	WM
13	1527739885	13	ICR	Resi	15	8	44.58	42.44	T2	D2	WM
14	1526426727	14	ICR	Comm	68	2	78.36	81.72	T3	D1	WM
15	1526778261	15	ICR	Comm	7	9	80.05	42.79	T2	D2	WM
16	1527457283	16	ICR	Resi	93	8	26.21	42.97	T1	D2	WM
17	1525397978	17	ICR	Comm	84	9	6.46	4.36	T4	D2	WM
18	1525624814	18	ICR	Resi	41	4	58.09	27.40	T3	D1	WM
19	1526213290	19	ICR	Resi	15	8	70.66	99.78	T3	D1	WM
20	1527573411	20	ICR	Comm	35	9	19.89	42.19	T1	D2	WM

So what I need is a calc to evaluate an individuals Avg Quality score against the Team he is in. And also a calc to compare against the Dept. And one for the org. 

EG, compare AgentID=1 against his Team of TeamID = T3.

Ive tried using something like:

AvgDeptQualityScoreComp = CALCULATE(AVERAGE(Table1[Quality]),ALL(Table1), Table1[TeamID])  - but it doesnt like the last filter as its looking for an explicit value or T/F.

I dont want the give TeamID a specific value as this will need to change depending on the AgentID filtered on the page.

[Anonymous]

Hi, this should do what you're after, it will only return a value when there is a filter applied to the agent id column. Let me know if it helps!

Team Average =  
   VAR Team =
        MAX ( Sales[TeamID] )
    RETURN
        IF (
            ISFILTERED ( Sales[AgentId] ),
            CALCULATE ( AVERAGE ( Sales[Quality] ), Sales[TeamID] = Team ),
            BLANK ()
        )

Department Average =  
   VAR Department =
        MAX ( Sales[DeptID] )
    RETURN
        IF (
            ISFILTERED ( Sales[AgentId] ),
            CALCULATE ( AVERAGE ( Sales[Quality] ), Sales[DeptID] = Department ),
            BLANK ()
        )

[rax99]

This didnt appear to wrk. But  think it could be an issue with my dataset. I have redone the data structure and will repost the question. However thanks for your help, and apologies.