How to Group Job Titles Into Broad Categories

[kennedy311]

Hi Everyone,

I have a list of about 366k job titles (83k unique) that I need to group into general categories. For example, titles like:

Chief Executive Officer

CEO

CMO

President

...need to go into an "Executives" group. Another example:

Director

Senior Manager

Mgr

Sr. Dir.

...need to go into a "Director/Manager" group. The idea being I can slice data by these groups once they're created.

The problem I have is that a lot of these job titles have several permutations within the data, so I can't find a relatively clean way to group these job titles together. See screenshot below...just filtering by "pres" I get 8,707 different job titles:

I have five groups that I am trying to create:

Executives

- CEO/COO/CFO/etc

- President

- Vice President

Directors/Managers

- Directors

- Managers

- Senior Directors

- Senior Managers

Legal Counsel

- Any job title that contains the word "Counsel"

Lawyers

- Partners

- Associates

Other

- Any titles not captures in the other four groups

I am happy to post some sample data if needed, but it's literally just a big list of job titles. I have tried a few solutions already, but get stuck when it comes to adding multiple search strings to a single group. Thanks in advance for the help!

[kennedy311]

@Greg_Deckler  I figured it out - for my use case anyway. I created a second calculated column using your proposed method, and then created a heirarchical slicer:

So my top line of the heirarchy contains my leadership bands (Senior Leadership, Business Pros - which now holds Directors, Manageers and Lawyers, and other). The next line of the heirarchy is my second calculated column where I call out the two subgroups I need to also identify - Legal Counsel and any HR Pros. Now I can see those executives that are both Vice President AND General Counsel.

This is VERY handy for anyone trying to aggregate a huge pile of job titles for analysis in Power BI. Thanks a million for the help!

[ArianMar]

I worked in the same position for quite a long time. I think it's good to think about changing jobs or even companies to develop your career. Many good vacancies, including DevOps jobs with relocation assistance I found on relocateme, and this helped me find a good job with the possibility of moving to another country. I've been thinking about this for a long time, but with the support of the company, it turned out to be much easier to implement.

[kennedy311]

@Greg_DecklerYou've got me cookin'! One follow-on question. There are some titles that I might want to appear in multiple buckets. For example, the title "Vice President and General Counsel" would ideally appear in both the Executives group and the Legal Counsel group. Is that possible using this method? Thank you for the quick reply!

[Greg_Deckler]

@kennedy311 Hmm, not as a calculated column.

[Greg_Deckler · Accepted solution]

@kennedy311 You might try something like this as a calculated column:

Column = 
  SWITCH(TRUE(),
    SEARCH("CEO", [Job Title],,0) > 0 ||
        SEARCH("COO", [Job Title],,0) > 0 ||
        SEARCH("CFO", [Job Title],,0) > 0 ||
        SEARCH("Chief", [Job Title],,0) > 0 ||
        SEARCH("President", [Job Title],,0) > 0,
        "Executives",
    SEARCH("Manager", [Job Title],,0) > 0 ||
        SEARCH("Director", [Job Title],,0) > 0,
        "Directors/Managers",
    SEARCH("Counsel", [Job Title],,0) > 0, "Legal Counsel",
    SEARCH("Partner", [Job Title],,0) > 0 ||
        SEARCH("Associate", [Job Title],,0) > 0,
        "Lawyers",
    "Other"
  )

 SEARCH is case insensitive.