Power BI - Find the % matching (Bounty 100) What-if analysis before and after

Question

I have a requirement where I have a table like this,

Role              Skills

Developer           C

Developer           SQL

Developer           C++

Data Analyst        R

Data Analyst        Python

Data Analyst        SQL

Business Analyst    Excel

Business Analyst    SQL

And I need to create something like this in Power BI,

 Explaining the first result for a Business Analyst in Power BI Visual Table,

1. From Filter 1 - I have selected Data Analyst - whose actual skills are R, Python and SQL
2. From Filter 2 - I have selected a new skill (Upskill) as Excel.

So now, he has 4 skills.

So For Business Analyst - Row 1 in Visual Table %Skills without upskilling - Only SQL from Data Analyst skill matches with Business Analyst skills, so its 50% before upskilling.

But after upskilling with excel it becomes 100% of the Business Analyst skills.

He has 4 skills after upskilling, but 2 of them (R and Python), he won't be using in Business Analyst Role.That is shown in the last column of the table.

The Idea is I am trying to show - How much percentage the skillsets of the selected role is matching with another role.

How much percentage the skillsets of selected role is matching with another role after upskilling.

Happy enough to create new tables based on this and re-shape the data however required.

Answer 1

• The key here is to have distinct unrelated tables for your slicers.

• Let's call your original table Jobs.

Create two new tables:

Role = DISTINCT(Jobs[Role])

Skills = DISTINCT(Jobs[Skills])

• Now that we have these tables, we can create slicers with them and read the selected values into our measures.

% Skill Match =

VAR SelectedRole = SELECTEDVALUE ( Role[Role] )

VAR RelatedSkills = CALCULATETABLE ( DISTINCT ( Jobs[Skills] ), Jobs[Role] = SelectedRole )

VAR CurrentSkills = DISTINCT ( Jobs[Skills] )

RETURN

    DIVIDE (

        COUNTROWS ( INTERSECT ( RelatedSkills, CurrentSkills ) ),

        COUNTROWS ( CurrentSkills )

    )

• This reads in your selected role in the first variable. When we upskill, we read in the other slicer as well:

% Skill Match Upskilled =

VAR SelectedRole = SELECTEDVALUE ( Role[Role] )

VAR SelectedSkills = VALUES ( Skills[Skills] )

VAR RelatedSkills = CALCULATETABLE ( DISTINCT ( Jobs[Skills] ), Jobs[Role] = SelectedRole )

VAR CurrentSkills = DISTINCT ( Jobs[Skills] )

VAR Upskilled = DISTINCT ( UNION ( RelatedSkills, SelectedSkills ) )

RETURN

    DIVIDE (

        COUNTROWS ( INTERSECT ( Upskilled, CurrentSkills ) ),

        COUNTROWS ( CurrentSkills )

    )

• The unused skill measure is very similar.

Unused Skills =

VAR SelectedRole = SELECTEDVALUE ( Role[Role] )

VAR SelectedSkills = VALUES ( Skills[Skills] )

VAR RelatedSkills = CALCULATETABLE ( DISTINCT ( Jobs[Skills] ), Jobs[Role] = SelectedRole )

VAR CurrentSkills = DISTINCT ( Jobs[Skills] )

VAR Upskilled = DISTINCT ( UNION ( RelatedSkills, SelectedSkills ) )

RETURN

    CONCATENATEX ( EXCEPT ( Upskilled, CurrentSkills ), Jobs[Skills], ", " )

• The result should look something like this:

• You can add some logic to hide the role you've selected in the matrix visual, but I'm keeping things simpler here.