cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vozniak Regular Visitor
Regular Visitor

Trying to calculate percent of employees being reviewed by date

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!

1 ACCEPTED SOLUTION

Accepted Solutions
robenanderson Frequent Visitor
Frequent Visitor

Re: Trying to calculate percent of employees being reviewed by date

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.

 

Capture.PNG

8 REPLIES 8
robenanderson Frequent Visitor
Frequent Visitor

Re: Trying to calculate percent of employees being reviewed by date

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 Regular Visitor
Regular Visitor

Re: Trying to calculate percent of employees being reviewed by date

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 Frequent Visitor
Frequent Visitor

Re: Trying to calculate percent of employees being reviewed by date

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 Regular Visitor
Regular Visitor

Re: Trying to calculate percent of employees being reviewed by date

 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

 

DateEmployeePassLeader
1/4/2018MaryTRUEJohn
1/6/2018SueTRUEMarsha
1/6/2018JaneTRUECarol
1/8/2018LizFALSECarol
1/11/2018KieshaTRUESusan
1/12/2018MaryTRUEJohn
1/12/2018KieshaTRUEMarsha
1/12/2018JaneFALSECarol
1/15/2018LizTRUEMarsha

                

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 Frequent Visitor
Frequent Visitor

Re: Trying to calculate percent of employees being reviewed by date

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 Regular Visitor
Regular Visitor

Re: Trying to calculate percent of employees being reviewed by date

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 Frequent Visitor
Frequent Visitor

Re: Trying to calculate percent of employees being reviewed by date

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.

 

Capture.PNG

 

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.

robenanderson Frequent Visitor
Frequent Visitor

Re: Trying to calculate percent of employees being reviewed by date

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.

 

Capture.PNG

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 206 members 1,699 guests
Please welcome our newest community members: