Solution Requested

[shantupm5]

Hello Everyone, 

I have a complex requirment which i am unable to resolve. Basicall i have 3 data set which is from 3 different source (Tables)

Source 1) Employee Id along with their Job Level

Source 2) Mandatory Training Module for each Job Level

Source 3) List of Employee Id's along with the Job Level who have completed 1 or more training from the Mandatory training Module list. 

Date set is attached below.

Requirement is, we are looking for Job Level wise, Mandatory training module wise completion rate. Please can you resolve and share this in PBIX format which will be of more help.

Data set: 

Source 1) Employee Id along with their Job Level

Emp Id	Job Level
100	2
200	3
300	2
400	2
500	3
600	4
700	3
800	3
900	4
1000	3
1100	3
1200	3
1300	3
1400	3
1500	4

Source 2) Mandatory Training Module for each Job Level

JL	Mandatory Training Module
2	Foundation - 2
2	Intermediate - 2
2	Pro - 2
3	Foundation - 3
3	Intermediate - 3
3	Pro - 3
4	Foundation - 4
4	Intermediate - 4
4	Pro - 4

Source 3) List of Employee Id's along with the Job Level who have completed 1 or more training from the Mandatory training Module list. 

Emp Id	JL	Training Module Completed
100	2	Foundation - 2
100	2	Intermediate - 2
100	2	Pro - 2
200	3	Foundation - 3
200	3	Intermediate - 3
300	2	Foundation - 2
400	2	Foundation - 2
400	2	Intermediate - 2
500	3	Foundation - 3
500	3	Intermediate - 3
600	4	Foundation - 4
700	3	Foundation - 3
800	3	Foundation - 3
800	3	Intermediate - 3
800	3	Pro - 3
900	4	Foundation - 4
900	4	Intermediate - 4
1000	3	Foundation - 3
1000	3	Intermediate - 3
1000	3	Pro - 3
1100	3
1200	3
1300	3
1400	3
1500	4

Thanks in advance

[Ashish_Mathur]

Hi,

Based on the 3 tables that you have shared, show the expected result very clearly in a Table format.  Still better would be share the download link of the PBI file with 4 tabs - one each for the Tables that yo have shared in your original post and the last one showing the expected result.

[shantupm5]

Hello Ashish,

Unfortunelty i will not be able to upload the PBIX due to security reasons.

so to illisturate below is the output we need from above table

For Example : For Job Leve 3, we have 9 employees. 

So each employee should complete all the mandatory trainings

But for Foundation - 3, only 5 have completed out of 9 employees. which will be 55.6% as mentioned below. 

JL	2	3	4
Foundation - 2	100%
Intermediate - 2	67.00%
Pro - 2	33.33%
Foundation - 3		55.60%
Intermediate - 3		44.40%
Pro - 3		22.20%
Foundation - 4			67.00%
Intermediate - 4			33.33%
Pro - 4

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Fowmy · Accepted solution]

@shantupm5 

Please check the attached file. 

Completion Rate = 
VAR __EmpJob = COUNT( EmpJob[Emp Id] )
VAR __EmpCount = COUNT( EmpJobModule[Emp Id])
RETURN
    DIVIDE(__EmpCount,__EmpJob)