Calculate percentage based on department plus filter

[Whibley92]

I have 2 sets of data with a unique identifier connecting the 2 data sets.

1 = course competion status

2 = department details

No.1 doesnt have the department in but that is what we want calculate.

I know I can merge the requirements in Transform Data but is there a way to write one formula that does it all.

1. Count completed status for each Department

2. Divide by all status in each department

So its within departments basically, not overall count of all departments.

[Whibley92]

Thanks yes I had but it was wrong....thanks this works great. 

[ryan_mayu]

could you pls proivde the sample data and expected output?

[Whibley92]

Data set 1

Name	Employee Number	Department
A	1	HR
B	2	Sales
C	3	HR
D	4	Marketing

Data set 2

Name	Employee Number	Course	Status	Completion Date
A	1	Statutory Training	In Progress
B	2	Statutory Training	Not Started
C	3	Statutory Training	Completed	01/05/2024
D	4	Statutory Training	Completed	12/04/2024

Outcome:

Department	Completion %	Overall %
HR	50%	25%
Sales	0%	0%
Marketing	100%	25%

[ryan_mayu · Accepted solution]

@Whibley92 

is sales 100% correct? the status is not statrted.

Measure = countx(FILTER(Table2,Table2[Status]="Completed"),Table2[Status])/COUNTROWS(Table2)+0

pls see the attachment below

[Whibley92]

Thanks, the 100% was my error.

It has just given 26.26% for all which is the overall completion percentage. How do I make it % within a department.

The code doesnt reference table 1 which has the department in, how does it connect?

[ryan_mayu]

have you built relationship between two tables?