Forum Discussion
Counting Names Across Multiple Columns but not Double Counting Names in the Same Row
Hi All,
I am running into a counting issue that I am not exactly sure how to solve. Please see below for context and a sample data table:
Context: I am attempting to build a job capacity planning dashboard that will tell me how many jobs each of my employees is working on at the moment (we are a staffing firm and each job is worked by a minuimum of a Recruiter and Search Leader, and a maximum of 2 Recruiters and 2 Search Leaders - beyond that, a single person can work as both the Recruiter and the Search Leader if needed). I need to be able to see how many jobs each employee is working, without double counting if someones name is listed as a Recruiter and a Search Leader for the same job number.
| Job Number | Recruiter 1 | Recruiter 2 | Search Leader 1 | Search Leader 2 |
| 15932 | Matt | Matt | ||
| 14956 | Gary | Tanya | Beth | Gary |
| 16905 | Tanya | Gary | ||
| 14544 | Paul | Randy | Gary |
My question is, how do I tally how many Jobs each individual is working, regardless of role? So for instance in the example above, Matt is only working 1 job (15932), Gary is working 3 jobs (14956, 16905, 14544), and Tanya is working 2 jobs (14956, 16905), etc. I have an additional data table of employee names availablie if needed, but I am not sure how I would link that to this original table because the names fall across 4 columns.
Happy to clarify any details if needed! Thanks so much in advanced!
jdellamag
Hi,
the solution for your problem is simple, but in Power Query M.
Select [Job Number] and from Ribbon > Transform > Unpivot Columns > Unpivot Other Columns
Note that if you have more information on your oryginal table, you should select all the columns that shoudn't be unpivoted.
Before:
After:
Filter out rows where Name is null, "" or " " (last one is space).
Results:
Additionaly you can add a new column with common Role to skip numer 1 or 2 at the end of Role to Job Number.
Select [Role to Job Number] and from Ribbon > Add Colmn > Extract > Text Defore Delimeter
Delimeter: (space)
Advanced options:
Scan for the delimeter: From the end of the imput
Number of delimeters to skip: 0
Final data:
All you need is simple measures:
Number of jobs = DISTINCTCOUNT(SampleTable[Job Number])Number of employees = DISTINCTCOUNT(SampleTable[Name])Results:
2 Replies
Hi,
the solution for your problem is simple, but in Power Query M.
Select [Job Number] and from Ribbon > Transform > Unpivot Columns > Unpivot Other Columns
Note that if you have more information on your oryginal table, you should select all the columns that shoudn't be unpivoted.
Before:
After:
Filter out rows where Name is null, "" or " " (last one is space).
Results:
Additionaly you can add a new column with common Role to skip numer 1 or 2 at the end of Role to Job Number.
Select [Role to Job Number] and from Ribbon > Add Colmn > Extract > Text Defore Delimeter
Delimeter: (space)
Advanced options:
Scan for the delimeter: From the end of the imput
Number of delimeters to skip: 0
Final data:
All you need is simple measures:
Number of jobs = DISTINCTCOUNT(SampleTable[Job Number])Number of employees = DISTINCTCOUNT(SampleTable[Name])Results: