求出MAX值的累积和 [英] Finding cummulative sum of MAX values

问题描述

我需要计算每个期间(或每个类别)的最大值的累积总和.查看嵌入的图像.

I need to calculate the cumulative sum of Max value per period (or per category). See the embedded image.

因此，首先，我需要找到每年每个类别/月的最大值.然后，我要计算这些最大值的累积总和.我尝试通过设置最大度量值来进行尝试(第一步工作得很好-找到给定年份的每个类别/月的最大值)，但后来我找不到找到累积总和的解决方案(发现累积最大值很简单，但是它很容易不是我要找的东西.

So, first, I need to find max value for each category/month per year. Then I want to calculate the cumulative SUM of these max values. I tried it by setting up max measure (which works fine for the first step - finding max per category/month for a given year) but then I fail at finding a solution to finding cumulative SUM (finding the cumulative Max is easy, but it is not what I'm looking for).

表1

Year  Month  MonthlyValue  MaxPerYear
2016  Jan    10            15
2016  Feb    15            15
2016  Mar    12            15
2017  Jan    22            22
2017  Feb    19            22
2017  Mar    12            22
2018  Jan     5            17
2018  Feb    16            17
2018  Mar    17            17

所需的输出

Year  CumSum
2016  15
2017  37
2018  54

推荐答案

这有点类似于此问题和此问题和这个问题就小计而言，还包括一个累积成分.

This is a bit similar to this question and this question and this question as far as subtotaling, but also includes a cumulative component as well.

您可以分两步执行此操作.首先，计算一张给出每年最大值的表格，然后使用累积总数模式.

You can do this in two steps. First, calculate a table that gives the max for each year and then use a cumulative total pattern.

CumSum = 
VAR Summary =
    SUMMARIZE(
        ALLSELECTED(Table1),
        Table1[Year],
        "Max",
        MAX(Table1[MonthlyValue])
    )
RETURN
    SUMX(
        FILTER(
            Summary, 
            Table1[Year] <= MAX(Table1[Year])
        ),
        [Max]
    )

这是输出:

如果您扩展到月份级别，则看起来像这样:

If you expand to the month level, then it looks like this:

请注意，如果您只需要小计即可工作，则将每一行保留为最大值(15、22、17， 54 )，而不是保留为最大值的累积和(15、37、54 54 )，那么您可以使用更简单的方法:

Note that if you only need the subtotal to work leaving each row as a max (15, 22, 17, 54) rather than as a cumulative sum of maxes (15, 37, 54, 54), then you can use a simpler approach:

MaxSum =
    SUMX(
        VALUES( Table1[Year] ),
        CALCULATE( MAX( Table1[MonthlyValue] ) )
    )

这将分别计算每年的最高金额，然后将其相加.

This calculates the max for each year separately and then adds them together.

外部参考:

• 小计和盛大正确"加起来的总数

累积总计-DAX模式

这篇关于求出MAX值的累积和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！