Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sachintha
Advocate I
Advocate I

How to calculated 'nested' measures?

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.

 

  1. Show the # tickets for each employee for each month.
  2. Show each employee's compliance (True/False) each month.
    • If a certain employee has submitted at least 1 ticket, they are compliant for that month, otherwise they are non-compliant.
  3. Show the compliant % of employees reporting to each manager.
    • If a certain manager has 3 employees reporting to them, and 2 of them have submitted at least 1 ticket for a month, that manager's % = 2/3 = 67%.
    • However, if a certain manager has other managers reporting to them, then the % must be calculated considering all subordinates.
      • In the data set, K and L report to X, and in turn, A, B, and C report to K and L. So, to calculate the number for X, we need to consider all A, B, C, K, and L.

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?

Matrix.png

 

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:

Comp.png

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?

 

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi, @Sachintha 

For your first need, show values (zeros) for employees who've never submitted a ticket.You just need to select this:

vyueyunzhmsft_0-1680147056428.png

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:

vyueyunzhmsft_1-1680147125446.png

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

 

View solution in original post

2 REPLIES 2
v-yueyunzh-msft
Community Support
Community Support

Hi, @Sachintha 

For your first need, show values (zeros) for employees who've never submitted a ticket.You just need to select this:

vyueyunzhmsft_0-1680147056428.png

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:

vyueyunzhmsft_1-1680147125446.png

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.