为什么我的SUMX DAX函数返回此结果? [英] Why is my SUMX DAX function returning this result?

问题描述

假设我有2张桌子:

f交易

  ProdID RepID收入1 1 101 1 101 2 10 

dSalesReps

  RepID RepName1乔2起诉 

在dSalesReps具有以下措施且尚未应用过滤器的情况下:

RepSales:= CALCULATE(SUM(fTransactions [Revenue]))
RepSales2:= SUMX(fTransactions，CALCULATE(SUM(fTransactions [Revenue]))

第一个措施执行了我的想法.转到fTransactions表并汇总收入"列.

第二种方法经过反复试验并弄清楚后，似乎将其本身分组在fTransactions中的唯一行上.在上面的示例中，fTransactions具有2行，其中所有内容都相同，然后是最后一行，其中内容有所不同.这似乎导致以下结果:

(10 + 10)对第一个分组"求和的第一次迭代
+
(10 + 10)第二次迭代，再次将第一个分组"求和
+
(10)对第二个分组"求和的最后一次迭代

= 20 + 20 + 10 = 50

至少这就是它的运行方式.我只是不明白为什么.我以为它将转到fTransactions表，将每次迭代的所有Revenue相加，然后将这些总和相加作为最后一步.

解决方案

这是由所谓的上下文转换"引起的(请参阅

要证明这一点，只需将rowID添加到事务表中:

这不会发生，因为等效的 filter上下文还包括RowID列，该列仅匹配一行

希望这会有所帮助，请使用sqlbi文章作为参考，这将是理解此内容的详尽指南

Suppose I have 2 tables:

fTransactions

ProdID  RepID  Revenue
1       1      10
1       1      10
1       2      10

dSalesReps

RepID   RepName
1       joe
2       sue

With dSalesReps having the following measures with no filters applied yet:

RepSales:=CALCULATE(SUM(fTransactions[Revenue]))
RepSales2:=SUMX(fTransactions, CALCULATE(SUM(fTransactions[Revenue]))

The first measure performs how I think it would. It goes to the fTransactions table and sums up the Revenue column.

The second measure, after a lot of trial and error to figure it out, seems to sort of group itself on unique rows in fTransactions. In the above example, fTransactions has 2 rows where everything is identical, then a last row where something is different. This seems to result in the following:

(10 + 10) first iteration that sums the first "grouping"
+
(10 + 10) second iteration that sums the first "grouping" again
+
(10) last iteration that sums the second "grouping"

= 20 + 20 + 10 = 50

At least that's how it looks to be operating. I just don't understand why. I thought it would go to the fTransactions table, sum all of Revenue for each iteration, then sum those sums as a final step.

解决方案

This is caused by something called "context-transition" (see sqlbi more detailed explanation).

In practice, your formula "RepSales" uses a "Row Context" (created by SUMX) which is turned in an equivalent "Filter Context" (by CALCULATE), but since you don't have an unique key in the table, it gets and uses multiple rows in each iteration, below the explanation.

For the first row, the row context is ProdID=1 AND RepID=1, which turned in an equivalent filter context (stays the same, in this case) is ProdID=1 AND RepID=1 but the filter context is global, and two rows (the first 2) match this filter. This is repeated for each row.

it does not happen with the formula "RepSales" because it does not iterate multiple times (as you already noticed)

This is your current situation:

To prove that, just add a rowID to the transaction table:

It does not happen because the equivalent filter context also include the RowID column, which matches only one row

Hope this helps, use the sqlbi article as a reference, it will be an exhaustive guide to understand this

这篇关于为什么我的SUMX DAX函数返回此结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！