Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to 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.
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.
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 🙂
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |