小节的总计 [英] Running total of a Measure

问题描述

我希望创建一个MEASURE的运行总计(此处名称为Open vs Closed).除了最后一个总公开赛"，我的所有情况都正确.任何人都知道有一种方法可以将打开"与关闭"列的总和作为总和?

i'm looking to create a running total of a MEASURE (here name Open vs Closed). I have all my colums correct except the last one "Total Open". Anybody knows a measure to have this as a running total of the Open vs Closed column ?

WeekIndex |Open Incidents | Closed Incidents | Open vs Closed | Total Open
1         | 5             | 0                | +5             | 5
2         | 4             | 5                | -1             | 4
3         | 2             | 0                | +2             | 6
4         | 3             | 3                | +0             | 6
5         | 10            | 12               | -2             | 4

推荐答案

在这种情况下，使用 EARLIER 函数的计算列可以做到:

A calculated column using the EARLIER function can do in this case:

Total Open = 
CALCULATE(
    SUM('Table'[Open vs Closed]),
    FILTER(
        'Table',
        'Table'[WeekIndex] <= EARLIER('Table'[WeekIndex])
    )
)

结果:

已更新:

在这种情况下，应采取以下措施.将 ALL 函数放在表中时，将忽略行级上下文:

The following measure should work in this case. The ALL function is needed to ignore the row level context when it's put in the table:

Total Open Measure = 
CALCULATE(
    [Open vs Closed Measure],
    FILTER(
        ALL('Table'),
        'Table'[WeekIndex] <= MAX('Table'[WeekIndex])
    )
)

第二次更新:

考虑到所有列都是度量值的奇怪情况:

Given the weird case that all columns are measures:

Total Open Measure = 
VAR CurrentIndex = [Index]
RETURN
CALCULATE(
    [Open vs Closed Measure],
    FILTER(
        ALL('Table'),
        [Index] <= CurrentIndex
    )
)

第三次更新:

我只能使用 MonthIndex 提出一种方法.我认为额外的 6 的原因是由于您计算 Incidents Closed 的方式.无论如何，我通过在过滤器中添加'Calendar'[MonthIndex]> == 1 来解决此问题:

I can only come up with a way using the MonthIndex. I think the reason of that extra 6 is due to the way you calculate Incidents Closed. Anyways I fix it by adding 'Calendar'[MonthIndex] >= 1 to the filter:

Total Open Measure = 
CALCULATE(
    [Open vs Closed],
    FILTER(
        ALL('Calendar'),
        'Calendar'[MonthIndex] >= 1 &&
        'Calendar'[MonthIndex] <= MAX('Calendar'[MonthIndex])
    )
)

要过滤掉行而不发生意外，我在 Index 度量上添加了一个可视级过滤器:

To filter out the rows without incidents I added a visual level filter on the Index measure:

这篇关于小节的总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！