CODEWITHSUNDEEP
powerbi
Mykenk
Mykenk

Reputation: 81

Running Total in Matrix Rows

I have incremental data elements that I want to summarize. I'm pulling the incremental data into a matrix object just fine, but I need to summarize them by cumulating across columns (within the Row)

What I'm seeing:

Column:    1       2       3       4       5
Row     |-----------------------------------------
1       | 10      15      5        4        1
2       | 12      12      3        1
3       | 10       9      6
4       |  9      15
5       | 11

What I want to see:

Column:    1       2       3       4       5
Row     |-----------------------------------------
1       | 10      25      30       34      35
2       | 12      24      27       28
3       | 10      19      25
4       |  9      24
5       | 11

What I've tried, this just returns the incremental data (as if I just pointed it to [INC_AMT]:

Cum_Loss = CALCULATE(
SUM('Table1'[INC_AMT]),
FILTER(All (Table1[ColNum]), Table1[ColNum] <= max(Table1[Column])))

Upvotes: 2

Views: 131

Answers (1)

Ryan B.
Ryan B.

Reputation: 3665

Give this measure a try:

PERIODIC INCREMENTAL SUM = CALCULATE
(
    SUM('TestData'[INC_AMT])
    , FILTER(
        ALLSELECTED(TestData)
        , and(
            TestData[ColNum] <= max(TestData[ColNum]) 
            , TestData[RowNum] = max(TestData[RowNum])
        )
    )
)

I found it helpful to not think about the measures in a matrix perspective. Transform it to a table and you see that one way to think about it is that it's just a cumulative sum where 'Row Number' is also the same. So, add that requirement to your filter and... presto.enter image description here

Upvotes: 1

Related Questions