topic Re: Calculate the percentage of employees in a specific branch who have completed courses in DAX Commands and Tips

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

DeniseDL — Thu, 13 Apr 2023 15:05:21 GMT

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!

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

johnt75 — Thu, 13 Apr 2023 15:48:03 GMT

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 %

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

DeniseDL — Mon, 17 Apr 2023 11:28:12 GMT

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!

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

johnt75 — Mon, 17 Apr 2023 12:59:19 GMT

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

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

DeniseDL — Tue, 18 Apr 2023 11:08:13 GMT

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.

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

johnt75 — Tue, 18 Apr 2023 12:41:14 GMT

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 ?

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

DeniseDL — Tue, 18 Apr 2023 14:18:32 GMT

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😁