Power BI - 查找匹配百分比(Bounty 100)之前和之后的假设分析 [英] Power BI - Find the % matching (Bounty 100) What-if analysis before and after

问题描述

我有一个要求，我有一张这样的桌子，

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

我需要在 Power BI 中创建类似的东西，

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

在 Power BI 可视化表中为业务分析师解释第一个结果，

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

1. 从筛选器 1 - 我选择了数据分析师 - 其实际技能是 R、Python 和 SQL
2. 从过滤器 2 - 我选择了一项新技能 (Upskill) 作为 Excel.

所以现在，他有 4 个技能.

So now, he has 4 skills.

对于业务分析师来说，可视化表中的第 1 行% 无需提升技能的技能 - 只有数据分析师技能中的 SQL 与业务分析师技能相匹配，因此在提升技能之前为 50%.

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.

但在使用 excel 提升技能后，它就变成了 100% 的业务分析师技能.

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

升级后他有 4 项技能，但其中 2 项(R 和 Python)，他不会在业务分析师角色中使用.这显示在表格的最后一列.

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.

推荐答案

这里的关键是为切片器提供不同的不相关表.

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

我们将您的原始表称为 Jobs.

Let's call your original table Jobs.

创建两个新表:

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], ", " )

结果应该是这样的:

您可以添加一些逻辑来隐藏您在矩阵视觉中选择的角色，但我在这里让事情变得更简单.

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

这篇关于Power BI - 查找匹配百分比(Bounty 100)之前和之后的假设分析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！