DAX query to compare against different categories

[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 table Sales1:

SalesID	Date	AgentId	Primary_Skill	Sub_Skill	Quality
1	1526848620	16021013918	ICR	Resi	50
2	1526384177	16021014176	ICR	Resi	5
3	1526021674	16021011104	ICR	Comm	21
4	1526110988	16301011025	ICR	Resi	59
5	1526353695	14027019026	ICR	Resi	62
6	1527599316	14121011035	ICR	Resi	88
7	1525586752	12141011090	ICR	Resi	14
8	1527552156	16021013921	ICR	Comm	24
9	1525499326	14027018065	ICR	Comm	56
10	1525660804	14121011013	ICR	Comm	41
11	1526524152	16021014177	ICR	Comm	10
12	1526424737	16021014153	ICR	Resi	18

This is the dummy AgentDetails table:

AGENTID	TeamID	DeptID	Org
16021013918	T1	D1	WM
16021014176	T1	D1	WM
16021011104	T2	D1	WM
16301011025	T2	D1	WM
14027019026	T3	D1	WM
14121011035	T3	D1	WM
12141011090	T4	D2	WM
16021013921	T4	D2	WM
14027018065	T5	D2	WM
14121011013	T5	D2	WM
16021014177	T6	D2	WM
16021014153	T6	D2	WM

In my report I have 3 filters, all mutli select. 

- DeptID

- TeamID

- AgentID

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

3 queries to compare Average Quality scores using following logic:

Calc 1. Compare AgentID=16021013918 against his Team of TeamID = T1

Calc 2. Compare AgentID=16021013918 against his Dept of DeptID = D1

Calc 3. Compare Team against the Dept it is in. So in this case compare Team = T1 against DeptID = D1

Ive tried using something like:

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

The idea is that I can compare an agent(s) score against his team, dept or the entire organisation. Also as a team leader I can compare my team against other teams in my department or even in other departments.

Let me know if further clarification is needed or if im simply asking too much for a single report to do. Or let me know if a sample workbook, packaged with the dummy data would help.

Thanks in advance for you help 🙂

[Seward12533 · Accepted solution]

If you allow only choice for selected "agentid" Try Something like  this

AvgDeptQualityScoreComp =
     VAR SelectedAgent = SELECTEDVALUE(Sales1[AgentID])
     VAR SelectedTeam = LOOKUPVALUE(AgentDetails1[TeamID],AgentDetails1[AgentID],SelectedAgent) RETURN
     IF(SelectedAgent,CALCULATE(AVERAGE(Sales1[Quality]),ALL(AgentDetails1), AgentDetails[TeamID]=SelectedTeam))

Notes:

*  the IF(SelectedAgent will block the calculation and return blank if no agent is selected or more than one selected.

* I didn't test it so might need a little tweaking but the idea should work.

[rax99]

Thanks this is the closest to what I need. Ideally I needed to be abl to select multiple agents. But this will work for now 🙂

[Johanno]

Hi, I played around with this. Don't know if I understood you correctly and if there are more suitable solutions, but here it is:

I created a measure for all agents:

Average all agents = CALCULATE(AVERAGE(Sales1[Quality]);ALL(Sales1[AgentId]))

And one measure for the selected agent:

Selected Agent = AVERAGE(Sales1[Quality])

I then created a hierarhy for Org - DeptID - TeamID so that I can drill down in the matrix (use drag and drop in the fields list).

Then I set up a slicer for AgentID and presented the result as a drill downed matrix.

That way you get an overview how the selected agent is compared to the rest.

/Johan

[rax99]

Thanks for your reply.  The only problem your solution is that selecting the item in matrix will update the all charts.

Basically my plan is to have 4 seperate bar charts all on the same report so a comparison can be made.

(using avgs)

1. selected Agent(s) score vs Team,

2. selected Agent(s) score vs Department

3. selected Agent(s) score vs Org

4. selected agent(s) score Team vs Department

So in this case I believe I need to have different cals for each chart. 

Hopefully I have explained well enough.

[Johanno]

Ok, could it be a solution to: Select the matrix - choose Format - Edit Interactions and then exclude the bar charts you don't want to be updated when you click in the matrix?

/Johan