Trying to calculate percent of employees being reviewed by date

[vozniak]

I have a list of employees being reviewed by date, the employees are identified by leader and by ID as well as the date of each review. I have a date table. I'm trying to show the percent of employees reviewed by date. I can show the overall percent reviewed, but I can't break it out to each specific date.

For example: there are 50 employees. I can show that overall, 50% have been reviewed. I can't tell that on 1/1/2018 20% were reviewed.

In addition, I can tell that 10% of Leader Mary's employees were reviewed, and what dates they were reviewed, but I can't check by date to see what percent of Mary's employees were reviewed that date.

I need to be able to pick a date and show the overall percent, and pick a date and a leader and show the percent of that leader's employees who were reviewed.

Any help would be greatly appreciated!

[robenanderson]

Have you created measures of the employees that have been reviewed and the total employees? If I'm understanding the whole question correctly, I think that could be the solution. Someone with more experience I'm sure could have a different answer. If you have the employees and what date they were reviewed on you could take a count of those that have been reviewed on that date, and divide that by the total employees measure.

[vozniak]

I've calculated the employees reviewed and the total number of employees, but I can't figure out how to relate the number of employees reviewed to the date they were reviewed. That's where I have the problem. If I could figure that out it would be simple to then calculate the percentage.

Thanks for your help! I do appreciate it.

[robenanderson]

does your data have a date in it? if so, then you may need to create a calendar table that you can put a relationship on. That way you can pull in MTD information. without seeing some of the data (or a sample with dummy info) I'm having a bit of a hard time understanding completely what the issue is.

[vozniak]

 I have a date in the data. I have a calendar table. I'm not trying to calculate month to date. I'm trying to look as a list of when activities took place and be able to pick a date and see all activities that took place on that date.

Example:

John has 5 employees total

Marsha has 4 employees total

Carol has 10 employees total

Susan has 3 employees total

Date	Employee	Pass	Leader
1/4/2018	Mary	TRUE	John
1/6/2018	Sue	TRUE	Marsha
1/6/2018	Jane	TRUE	Carol
1/8/2018	Liz	FALSE	Carol
1/11/2018	Kiesha	TRUE	Susan
1/12/2018	Mary	TRUE	John
1/12/2018	Kiesha	TRUE	Marsha
1/12/2018	Jane	FALSE	Carol
1/15/2018	Liz	TRUE	Marsha

On 1/12/2018 how what percentage of the employees were reviewed? What percentage of each leader's employees were reviewed?

I can figure out the total number of employees, the number that have passed or not passed (true/false), and the overall percent that have been reviewed and that have passed, but I can't figure out how to identify the percent reviewed on each specific date.

[robenanderson]

for the 1/12/2018, what is the number you are looking for? Are you looking for 66.7% of the people passed of reviewed (2/3)? Or are you looking for 9% (2/22)?

for reviewed I assume it's 14% (3/22)? Is that correct?

[vozniak]

I think I finally explained it properly! Yes, for reviewed I'm looking for 14% (3 of 22)

I also need to know what percent of each leader's employees were reviewed on a given day, such as 1 of 10 for Carol and 1 of 5 for John on 1/12.

I want to be able to look at 1/12 and know what % overall and, separately, what % for each leader. Once I have those, I can figure out what % passed or failed on that date.

[robenanderson · Accepted solution]

Let me know if this helps.

I entered your data into a Table (Table1). Created an if statement where true = 1, false/other = 0 (Pass/Fail). Reviewed column = 1.

I entered in the number of reports for each leader to a new table (Table2), then created a relationship between Table1 and Table2 via the Leader column.

I created a measure for Total Reports for the Leader table. meas.TotalReports = SUM(Table2[Reports])

I created measures for the Pass/Fail and Reviewed columns in Table1.

meas.PassFail = SUM(Table1[Pass/Fail])

meas.Reviewed = SUM(Table1[Reviewed])

Then, to get % Pass, divided meas.PassFail by meas.TotalReports, then the same for % Reviewed. Do these calculations as measures. Here's a snapshot of what I came up with.

[robenanderson]

So here's what I did with the sample data you provided. Pictures below. data is in Table 1 for me.

I created a column that calls out True as 1, false as 0, so those could be summed (Pass/Fail). I also created a reviewed column, which I just set to 1 (Reviewed).

I created another table (Table2) with each leader and their direct reports. A relationship appeared automatically on these, but if it doesn't, put it on the leader columns.

I then created measures. On the leader table, I put in a formula that looks like this: meas.TotalReports = SUM(Table2[Reports])

I created a measure for PassFail and Reviewed on the employee table. Similar to the one for total reports, summing up the Pass/Fail (if statement) and the reviewed.

Next, I created measures for % pass and % reviewed.

meas.%Pass = DIVIDE([meas.PassFail],[meas.TotalReports])

meas.%Reviewed = DIVIDE([meas.Reviewed],[meas.TotalReports])

Then, I put the tables together and we get precentages. You can adjust the formulas to replace null values with 0 or whatever you want. I just left them blank for ease of use.

I suspect you were trying to use columns for this. measures are best with these kinds of calculations. You shoudl be able to filter and do what you need to do with the data if you set up the measures like I did here.

Hope this helps.