excel DAX一对多关系中列之间的差异

92dk7w1h  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(98)

我正在努力使用DAX度量(或者计算列?)来获得一对多关系中两列之间的差异。
这里有一个例子
表格包含按任务(任务1和任务2)划分的所需活动。
另一张table上坐满了员工,他们在不同的任务上工作一部分时间。
我被要求提供任务所需时间(在required_activities表中)和实际时间(在employee表中)之间的差异
数据模型:


的数据
表格:



所以我想得到一个像这样的数据透视表:



如何构建计算difference (employee real times - required_activities times)的度量?我尝试使用RELATED函数,但无法使其工作。

qfe3c7zg

qfe3c7zg1#

有两种方法,方法1会让你更快,但有局限性-而方法2需要一点努力来塑造你的数据。
方法1直接回答你的问题。
方法2是推荐的方法。

方法一

如果任务数量有限,请尝试以下操作:
向数据模型中添加一个新表。我们称之为task,如下所示:
| 任务|
| --|
| 任务1|
| 任务2|
| 任务3|
单列表,每个任务名称对应一行。不需要关系。
接下来,将这三个度量添加到模型中。

required:=
  var task1 = SUM(required_activites[task 1])
  var task2 = SUM(required_activites[task 2])
  var task3 = SUM(required_activites[task 3])
  RETURN SWITCH( SELECTEDVALUE('task'[Task]),
    "task 1", task1,
    "task 2", task2,
    "task 3", task3,
    task1 + task2 + task3
  )

real:=
  var task1 = SUM(employees[task 1])
  var task2 = SUM(employees[task 2])
  var task3 = SUM(employees[task 3])
  RETURN SWITCH( SELECTEDVALUE('task'[Task]),
    "task 1", task1,
    "task 2", task2,
    "task 3", task3,
    task1 + task2 + task3
  )

diff:= [real] - [required]

字符串

更新

注意:如果您的Excel版本不支持SELECTEDVALUE,请将其替换为:

RETURN SWITCH( IF( HASONEVALUE('task'[Task]), VALUES('task'[Task]) ) ,


确保你在数据透视表中添加了'task'[Task]

方法二

另一种方法需要更改数据模型的形状,您需要使用Power Query来完成此操作。此方法是推荐的方法,因为它将以最佳方式进行扩展和执行。(请参阅“星星模式”)
对于这两个表,选择所有的任务列并取消透视,这样你就得到了这样的结构:
| 活动id|任务|值|
| --|--|--|
| 一|任务1| 0.5|
| 一|任务2| 0.5|
| B|任务1| 0.2|
| B|任务2| 0.3|

更新

在PowerQuery中,选择您的required_activities查询/表(已取消透视的查询/表),右键单击并将Duplicate.turn这个新查询转换为Dim Activity

  • 右键单击activity id列标题,然后选择Remove Other Columns
  • 再次右键单击列标题并选择Remove Duplicates

再次选择您的required_activities查询/表,右键单击并Duplicate。将此新查询重命名为Dim Task。按照相同的步骤操作,这样您就得到了一个没有重复项的Task列。(注意,此步骤消除了对方法1中提到的早期task表的需要。
在您的数据模型(Power Pivot)中,创建如下关系:
x1c 0d1x的数据
一旦你有了它,DAX的措施变得超级简单:

required:= SUM(required_activites[Value])

real:=SUM(employees[Value])

diff:= [real] - [required]

更新

在您的表格和图表中,使用Dim ActivityDim Task作为行/列等.

相关问题