Forum Discussion
Solution Requested
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
4 Replies
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.
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
