Forum Discussion
Data visualization based on Job Title Specifications
Hello fellow BI advocates...
I am currently in the process of creating a dahsboard on employee activity based on reports generated through our training software. The only issue is I must compare the modules which employees have taken/been assigned (be it complete or incomplete) - this data is generated through the reports I pull - to the modules which are required for their Job Title and outlined in their training plan. The issue I am facing concerns the latter part of my previous sentence (the data in the matrix) because the formatting does not translate so well when I try to model it.
Below I have attached a picture of the matrix and a picture of the report generated from our training software.
The matrix designates a column on the very Left, 'Job Title', which includes most of the job titles involved when we hire a new employee. All of the columns to the right of the 'Job Title' column represent 85+ courses that are a combination of all of the courses required for every Job Title. In each row, under these 'course' columns, there is a 1 or 0. The 1 signifies that the row-specific Job Title is required to take that course in-training. The 0 means any employees with those Job Titles do not need to complete it.
In the picture of the report I have attached, the image is truncated, but, I can see every new hire we've had since January, their activity, and which courses they have and have not completed. I have also created in an index function in that report to return the specific Job Title that person is, so I can connect it to the Job Title column in the matrix data. On a side note, it is worthwhile to note that the column 'Course Name' in the report are the same courses outlined as headers in the data matrix. I could not figure out how to create a relationship between row values and multiple column headers (I don't think you can).
My main issue is trying to visualize this data to effectively represent
a. a comparison of the courses required for their position to the courses they have and haven't taken (be mindful there are 85+ courses so this may be a challenge visually).
b. to show which courses they are missing for reference (this is so senior management can easily see the person and what they are still missing).
If anyone has any advice or a solution for the way I should go about this, I would be forever thankful!
Many Thanks
- Christian
I would start by unpivoting the first table. Here's an example:
1. Raw data:
2. In Power Query, right-click the Title column and select Unpivot Other Columns:
3. Rename the columns and filter Required = 1. This is the list of required courses by title.
A star schema works best in Power BI, so I would create a Course dimension table (one row per course), and create relationships to the other two tables. Once you get this set up, please share a sample pbix via one of the file services like OneDrive and we can delve into the DAX. 🙂
1 Reply
I would start by unpivoting the first table. Here's an example:
1. Raw data:
2. In Power Query, right-click the Title column and select Unpivot Other Columns:
3. Rename the columns and filter Required = 1. This is the list of required courses by title.
A star schema works best in Power BI, so I would create a Course dimension table (one row per course), and create relationships to the other two tables. Once you get this set up, please share a sample pbix via one of the file services like OneDrive and we can delve into the DAX. 🙂