CODEWITHSUNDEEP
maxpowerbidax
Lebo44
Lebo44

Reputation: 33

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).

Table1

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

Desired Output

Year  CumSum
2016  15
2017  37
2018  54

Upvotes: 1

Views: 2559

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40224

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]
    )

Here's the output:

CumSum

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

CumSum Monthly

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.

External References:

Upvotes: 2

Related Questions