DAX 中的运行/累积总计仅使用度量,没有计算列 [英] Running / Cumulative Total in DAX using only measures, no calculated columns
问题描述
问题:
创建一个不引用任何计算列,仅引用度量的运行总计度量.
Create a running total measure that does not reference any calculated columns, only measures.
一种度量是一种计算.另一种措施是自动递增从 RANKX 创建的 ID.
One measure is a calculation. Another measure is auto-incrementing IDs created from a RANKX.
目标是仅使用 B 的参考为 A 创建一个运行总度量/总和.
The goal is to create a running total measure/sum for A using reference only to B.
上下文:出于数据限制的原因,无法添加计算列.因此,已经创建了许多措施.
Context: For data restriction reasons, there is no ability to add a calculated column. Therefore, there are a number of measures that have been created.
现有试验:
现有的知识库(互联网搜索)似乎只涉及列和度量混合的度量.但是,无法添加计算列,也不需要/使用此特定运行总计中的现有列.
Existing knowledge base (internet searches) only seem to refer to measures where there is a mixture of columns and measures. There is no ability to add calculated columns, however, nor need for/use of existing columns within this particular running total.
目前已尝试使用表表达式来构建包含度量的表,为最大值 (MAXX) 创建附加变量并尝试以这种方式过滤并在返回中使用 CAlCULATE.这只是返回总数而不是运行总数.
Currently there has been an attempt to use a table expression to build the table with the measures, creating additional variables for a max (MAXX) and trying to filter this way and use a CAlCULATE in the return. This is only returning the total and not a running total.
示例表:
[...现有的列] | 措施 A | 措施 B | (需要)措施 C |
---|---|---|---|
... | 10 | 1 | 10 |
... | 60 | 2 | 70 |
... | 40 | 3 | 110 |
度量 A 是其他度量的总和度量 B 是其他度量的等级度量 C 是给定度量 B 的度量 A 的运行总计
Measure A is a sum of other measures Measure B is a rank of other measures Measure C is a running total of measure A given Measure B
推荐答案
这个的基本模式是:
CumulativeMeasureA =
VAR CurrentRank = [MeasureB]
RETURN
SUMX ( FILTER ( ALL ( Data[Group] ), [MeasureB] <= CurrentRank ), [MeasureA] )
其中 Data[Group]
是您在报表视觉对象中分组的列.
Where Data[Group]
is the column that you are grouping on in your report visual.
请注意,如果没有计算列,这不是很有效,因为它必须为视觉中每一行的每个 Group
计算 [MeasureB]
,过滤这些结果,并计算 [MeasureA]
每次未过滤掉的迭代.对于小型数据集,这不是问题,但随着事情变得更大和更复杂,这可能会成为问题.
Note that this isn't very efficient without calculated columns since it is having to compute [MeasureB]
for every Group
for every row in your visual, filter those results, and compute [MeasureA]
every iteration that isn't filtered out. For small datasets, this isn't a problem but can be as things get larger and more complex.
这篇关于DAX 中的运行/累积总计仅使用度量,没有计算列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!