在PowerPivot / DAX中总结相关表的值 [英] Summing up a related table's values in PowerPivot/DAX

问题描述

说我有两张桌子。 attrsTable：

 文件|属性|值
 ------------------------ 
 A | xdim | 5 
 A | ydim | 6 
 B | xdim | 7 
 B | ydim | 3 
 B | zdim | 2 
 C | xdim | 1 
 C | ydim | 7 
  

sizeTable：

  file | size 
 ----------- 
 A | 17 
 B | 23 
 C | 34 
  

我有这些表通过文件字段相关。我想在attrsTable中的PowerPivot测量，其计算使用大小。例如，假设我想为A，B，C中的每一个设置xdim + ydim / size。计算将是：

  A：（5 + 6）/ 17 
 B：（7 + 3）/ 23 
 C：（1 + 7）/ 34 
  

我希望该度量足够通用，所以我可以稍后使用切片器将文件或属性切片。我如何做到这一点？

我试过：

  dimPerSize： = CALCULATE（[value] / SUM（sizeTable [size]））＃计算0 
 dimPerSize：= CALCULATE（[value] / SUM（RELATED（sizeTable [size]）））＃生成错误
任何想法我在做错什么？/ / code> 

我可能在这里遗漏了一些关于如何使用DAX与关系的基本概念。

解决方案

Hi Redstreet， strong>

从您的解决方案和Jacob提出的解决方案中退出，我认为创建另一个可以汇总所有计算的表（特别是给定你可能有超过2个表格，具有文件特定的属性）。

所以我创建了一个包含唯一文件名的表，以这种方式可视化：

添加必要的措施要简单得多（不需要计算列）。我实际上测试了两种情况：

1）为属性值和文件大小创建简单的SUM度量。然后将这两个度量和作业完成分开： - ）。

2）使用SUMX函数有更多的通用解决方案。那么计算的最终公式如下：

  = DIVIDE（
 SUMX（DISTINCT（fileTable [file]），[AttrValue的总和]），
 SUMX（DISTINCT（fileTable [file]），[SumSize]），
 BLANK（）
） 
  

具有[Sumr AttrValue]：

  = SUM（attrsTable [value]）
  

的FileSize是：

  = SUM（sizeTable [size]）
  

尽管这两种情况下的SUMX都可以覆盖给定文件名的所有实例，但这样做的效果非常好。因此，对于文件 B ，它也使用 zdim 进行计算（如果需要过滤掉它，则使用简单的计算/过滤器组合）。在文件大小的情况下，我也使用SUMX，尽管它不是真的需要，因为表格包含每个文件名只有1个记录。如果有2个实例，则根据所需的结果使用SUMX或AVERAGEX。

这是Excel（2010）中我的源文件的链接

希望这有帮助。

Say I have two tables. attrsTable:

file | attribute | value
------------------------
A    | xdim      | 5
A    | ydim      | 6
B    | xdim      | 7
B    | ydim      | 3
B    | zdim      | 2
C    | xdim      | 1
C    | ydim      | 7

sizeTable:

file | size
-----------
A    | 17
B    | 23
C    | 34

I have these tables related via the 'file' field. I want a PowerPivot measure within attrsTable, whose calculation uses size. For example, let's say I want xdim+ydim/size for each of A, B, C. The calculations would be:

A: (5+6)/17
B: (7+3)/23
C: (1+7)/34

I want the measure to be generic enough so I can use slicers later on to slice by file or attribute. How do I accomplish this?

I tried:

dimPerSize := CALCULATE([value]/SUM(sizeTable[size])) # Calculates 0
dimPerSize := CALCULATE([value]/SUM(RELATED(sizeTable[size]))) # Produces an error

Any idea what I'm doing wrong? I'm probably missing some fundamental concepts here of how to use DAX with relationships.

解决方案

Hi Redstreet,

taking a step back from your solution and the one proposed by Jacob, I think it might be useful to create another table that would aggregate all the calculations (especially given you probably have more than 2 tables with file-specific attributes).

So I have created one more table that contains (only) unique file names, and thus the relationships could be visualized this way:

It's much simpler to add necessary measures (no need for calculated columns). I have actually tested 2 scenarios:

1) create simple SUM measures for both Attribute Value and File Size. Then divide those two measures and job done :-).

2) use SUMX functions to have a bit more universal solution. Then the final formula for DimPerSize calculation could look like this:

=DIVIDE(
   SUMX(DISTINCT(fileTable[file]),[Sum of AttrValue]), 
   SUMX(DISTINCT(fileTable[file]),[Sum of FileSize]), 
   BLANK()
)

With [Sum of AttrValue] being:

=SUM(attrsTable[value])

And Sum of FileSize being:

=SUM(sizeTable[size])

This worked perfectly fine, even though SUMX in both cases goes over all instances of given file name. So for file B it also calculates with zdim (if there is a need to filter this out, then use simple calculate / filter combination). In case of file size, I am using SUMX as well, even though it's not really needed since the table contains only 1 record for each file name. If there would be 2 instances, then use SUMX or AVERAGEX depending on the desired outcome.

This is the link to my source file in Excel (2010).

Hope this helps.

这篇关于在PowerPivot / DAX中总结相关表的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！