cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rax99 Member
Member

DAX query to compare against different categories

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:

 

SalesIDDateAgentIdPrimary_SkillSub_SkillQuality
1152684862016021013918ICRResi50
2152638417716021014176ICRResi5
3152602167416021011104ICRComm21
4152611098816301011025ICRResi59
5152635369514027019026ICRResi62
6152759931614121011035ICRResi88
7152558675212141011090ICRResi14
8152755215616021013921ICRComm24
9152549932614027018065ICRComm56
10152566080414121011013ICRComm41
11152652415216021014177ICRComm10
12152642473716021014153ICRResi18

 

 

This is the dummy AgentDetails table:

 

AGENTIDTeamIDDeptIDOrg
16021013918T1D1WM
16021014176T1D1WM
16021011104T2D1WM
16301011025T2D1WM
14027019026T3D1WM
14121011035T3D1WM
12141011090T4D2WM
16021013921T4D2WM
14027018065T5D2WM
14121011013T5D2WM
16021014177T6D2WM
16021014153T6D2WM

 

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 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Seward12533 New Contributor
New Contributor

Re: DAX query to compare against different categories

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.

View solution in original post

5 REPLIES 5
Johanno Member
Member

Re: DAX query to compare against different categories

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.

 

Power forum.PNG

 

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

 

/Johan

rax99 Member
Member

Re: DAX query to compare against different categories

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 Member
Member

Re: DAX query to compare against different categories

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

Seward12533 New Contributor
New Contributor

Re: DAX query to compare against different categories

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.

View solution in original post

rax99 Member
Member

Re: DAX query to compare against different categories

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 🙂

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 256 members 2,792 guests
Please welcome our newest community members: