Power BI-查找匹配前后的百分比匹配（赏金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 Visual Table中业务分析师的第一个结果，

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

1. 从过滤器1-我选择了数据分析师-实际技能是R，Python和SQL


2. 从过滤器2-我选择了

因此，他现在有4种技能。

So now, he has 4 skills.

因此对于业务分析师-行可视表中的1个
％技能无需升级-只有Data Analyst技能中的SQL与Business Analyst技能匹配，因此升级前为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.

足以创建

推荐答案

此处的关键是为您提供不同的不相关表

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

让我们调用原始表 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 )
    )

未使用的技能度量非常相似。 / p>

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-查找匹配前后的百分比匹配（赏金100）假设分析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！