Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
In my company, I have a 'ticket' list that the employees submit tickets to. The expectation is at minimum 1 ticket per month per employee (except for the President). I have also got a list of Employee-Manager relationships (essentially, an Org Chart) in the following format.
Ticket List:
ID,Date,Employee,Manager
A01,1/1/2023,A,K
A02,1/2/2023,B,K
A03,1/2/2023,F,M
A04,1/3/2023,B,K
A05,1/6/2023,F,M
A06,1/6/2023,F,M
A07,1/7/2023,L,X
A08,1/7/2023,M,Y
A09,1/8/2023,A,K
A10,1/9/2023,M,Y
A11,1/9/2023,X,Z
A12,1/10/2023,A,K
A13,1/14/2023,A,K
A14,1/16/2023,B,K
A15,1/19/2023,C,L
A16,1/22/2023,D,M
A17,1/23/2023,D,M
A18,1/24/2023,F,M
A19,1/26/2023,A,K
A20,1/26/2023,M,Y
A21,1/29/2023,X,Z
A22,1/30/2023,X,Z
A23,2/4/2023,C,L
A24,2/5/2023,B,K
A25,2/11/2023,B,K
A26,2/12/2023,A,K
A27,2/19/2023,M,Y
A28,2/19/2023,X,Z
A29,2/22/2023,Y,Z
A30,2/25/2023,X,Z
A31,2/27/2023,D,M
A32,2/27/2023,C,L
A33,3/2/2023,C,L
A34,3/2/2023,D,M
A35,3/6/2023,F,M
A36,3/14/2023,M,Y
A37,3/14/2023,D,M
A38,3/17/2023,A,K
A39,3/21/2023,C,L
A40,3/21/2023,D,M
A41,3/23/2023,X,Z
A42,3/23/2023,D,M
A43,3/28/2023,Y,Z
Org Chart:
Employee,Manager
A,K
B,K
C,L
D,M
E,M
F,M
K,X
L,X
M,Y
X,Z
Y,Z
Note that the reason for two tables is that although expected, some employees may not submit tickets every month (E.g. employees E and K have not submitted any for the entire period). So to have complete data I have this second Org Chart. The two are linked by 'Employee' column.
Now, my requirement is to create two charts.
Here's what I've done so far.
Measure to calculate tickets by employees:
Tickets = DISTINCTCOUNT(TicketList[ID])
This works fine, but I put this in a table (Employee and Manager coming from Org Chart), it doens't show values (zeros) for employees who've never submitted a ticket. In this case, K and E). How do I get those employees to show up?
Measure to check if an employee is compliant:
Is Compliant =
IF(TicketList[Tickets] >= 1,
TRUE(),
FALSE()
)
This works fine, and I can now show True/False compliance in a matrix.
But, now I run into problems when calculating the compliance % for a manager.
In the Org Chart, I have the following two measure to calculate it:
#Employees = DISTINCTCOUNT('Org Chart'[Employee])
Compliance % = DIVIDE(CALCULATE(DISTINCTCOUNT(TicketList[Employee]), FILTER(TicketList, [Is Compliant] = TRUE())), 'Org Chart'[#Employees])
But, this obviously calculates the % considering only the immediate subordinates:
How do I alter the measure to calculate the considering all employees reporting to them. For example, for X the number should be 80% for January (K non-compliant, L, A, B, C compliant).
Also, how can I get 0 to appear instead of blanks?
Solved! Go to Solution.
Hi, @Sachintha
For your first need, show values (zeros) for employees who've never submitted a ticket.You just need to select this:
For your second need , here are the steps you can refer to .
We need to create a calculated column like this:
Year_month = YEAR([Date])*100+MONTH([Date])
Then we can create a measure like this:
Measure =
var _cur_year_month =CALCULATE( MAX('TicketList'[Year_month]), ALL('test2'[Manager]))
var _em1= VALUES('test2'[Employee])
var _em2 = CALCULATETABLE( VALUES(test2[Employee]) , 'test2'[Manager] in _em1,ALLSELECTED('test2'))
var _em3 = UNION(_em1,_em2)
var _total = COUNTROWS(_em3)
var _test =ADDCOLUMNS( CROSSJOIN( ALLSELECTED('test2'[Employee]) , ALLSELECTED('TicketList'[Year_month])) ,"flag" , CALCULATE(DISTINCTCOUNT('TicketList'[ID]),TREATAS({[Employee]},TicketList[Employee]),TREATAS({[Year_month]},'TicketList'[Year_month]) ,ALL('test2'[Manager]) ))
var _t2 = FILTER(_test, [flag] <> BLANK() && [Year_month]=_cur_year_month && [Employee] in _em3)
return
COUNTROWS( _t2) / COUNTROWS(_em3)
Then we can put this measure on the visual and we can meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, @Sachintha
For your first need, show values (zeros) for employees who've never submitted a ticket.You just need to select this:
For your second need , here are the steps you can refer to .
We need to create a calculated column like this:
Year_month = YEAR([Date])*100+MONTH([Date])
Then we can create a measure like this:
Measure =
var _cur_year_month =CALCULATE( MAX('TicketList'[Year_month]), ALL('test2'[Manager]))
var _em1= VALUES('test2'[Employee])
var _em2 = CALCULATETABLE( VALUES(test2[Employee]) , 'test2'[Manager] in _em1,ALLSELECTED('test2'))
var _em3 = UNION(_em1,_em2)
var _total = COUNTROWS(_em3)
var _test =ADDCOLUMNS( CROSSJOIN( ALLSELECTED('test2'[Employee]) , ALLSELECTED('TicketList'[Year_month])) ,"flag" , CALCULATE(DISTINCTCOUNT('TicketList'[ID]),TREATAS({[Employee]},TicketList[Employee]),TREATAS({[Year_month]},'TicketList'[Year_month]) ,ALL('test2'[Manager]) ))
var _t2 = FILTER(_test, [flag] <> BLANK() && [Year_month]=_cur_year_month && [Employee] in _em3)
return
COUNTROWS( _t2) / COUNTROWS(_em3)
Then we can put this measure on the visual and we can meet your need:
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@v-yueyunzh-msft this worked perfectly, thanks!
However, I do not quite understand the complex measure, could you please explain it?
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |