Calculate the percentage of employees in a specific branch who have completed courses

[DeniseDL]

Hello,

HELP!!

I am fairly new to Power BI and I am having trouble figuring out how to create the correct measure for what I need to show.

I need to show the percentage of employees in the region & branch who have completed courses and then show who those people are.

I have:

CSPS School table - shows all completed courses and which employees have completed them 

MyGCHR HR Table - shows all employees in the department by region and branch

What I have done is created 3 measures: 

Total Employees = COUNT('MyGCHR report'[Full Name]) 

Total Courses Completed = COUNT('CSPS Report'[Transcript Status])

% Completed = [Total Employees] / [Total Completed]

The results that I'm getting are almost correct.  I'm showing:

Region (correct)

     Branch (correct)

          # of employees who have completed courses (correct)  

                 List of Courses each employee has taken

The percentages are incorrect - this is where I'm struggling - there are way more than 8 employees in the P&C branch so the percentage is obviously incorrect.  I want to show the percentage of employees in the region & branch who have completed courses and then show who those people are.

Any advice would be great!

Thank you!

[DeniseDL]

Thank you for the reply.  However, I seem to be getting a weird result and I can't figure out why.

When I have no date filter applied, the percentage comes out correctly (Total employees who have completed courses / Total employees). Figures marked in yellow.

However, when I apply the date filter to reflect our fiscal year of April 1/22 - Mar 31/23, the percentage isn't correct.

7900 / 16036 doesn't add up to 109.87%

The measures I am using are:

Total employees - All = DISTINCTCOUNT('MyGCHR report'[EmployeeID])

Total employees who completed courses = DISTINCTCOUNT('CSPS Report'[Empoyee ID])

% Employees completed courses = [Total employees who completed courses]/[Total employees - All]

I'm sure it's something silly that I'm missing.  If anyone has any suggestions, I would love to hear them as I am stuck on this one thing.

Thank you!

[johnt75]

Is there a relationship between your date table and 'MyGCHR report' ? If there is, check for blank values in the 'MyGCHR report' column.

[DeniseDL]

No there isn't a relationship between the date table and the MyGCHR report.  I also made sure there were no blanks in the CSPS report.  It seems to work fine until I change the date.

[johnt75 · Accepted solution]

Can you change the relationship between CSPS and MyGCHR Report to single direction, so that CSPS filters MyGCHR Report and not the other way around ?

[DeniseDL]

Woohoo!  This worked.  Turns out there were duplicates in the MyGCHR report that shouldn't have been there and once I removed them and changed the cardinality, it all worked great!  Thank you so much for all your help with this😁

[johnt75]

Assuming that employees can complete more than one course, I think the measure for the number of employees who have completed a course should be

Employees completed courses =
DISTINCTCOUNT ( 'CSPS Report'[Employee ID] )

You can then use that measure in your calculation of the %