Cumulative Sum over grouped by Column

[sjelting]

Hello,

i have a dataset looking like:

date,category,amount

0,2,1

1,2,3

1,2,4

4,5,6

the amount should be summed up by category in a certain interval, so that it looks like this if date >= 1:

category, amount

2,7

5,6

and now i want to add a column 'cumulative_amount':

category, amount, cumulative_amount

2,7,7

5,6,13

after trying so many very similar suggestions i still cant get it to work.

currently, i am trying:

Measure 1:

1 Sum of Amount = SUM(DATA[amount])+0

Measure 2:

2 Cumulative Measure =

VAR currentsum = [1 Sum of Amount ]

VAR currentperiod =

    MAX ( DATA[date] )

VAR currentperiodmin = MIN ( DATA[date] )

VAR result =

    CALCULATE (

        [1 Sum of Amount],

        FILTER (

            ALL ( Data), [1 Sum of Amount] >= currentsum && DATA[date] <= currentperiod && DATA[date] >= currentperiodmin

        )

    )

RETURN

    result

###

Whats the right way to fix this?

[tamerj1 · Accepted solution]

Hi @sjelting 
Please refer to attached sample file with the solution

Measure = 
VAR CurrentAmount = SUM ( q[amount] )
RETURN
    SUMX (
        FILTER (
            ALLSELECTED ( q[category] ),
            CALCULATE ( SUM ( q[amount] ) ) <= CurrentAmount
        ),
        CALCULATE ( SUM ( q[amount] ) )
    )

[Anonymous]

Hi  @sjelting ,

The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user interaction in the report. A measure operates on aggregations of data defined by the current context, which depends on the filter applied in the report – such as slicer, rows, and columns selection in a pivot table, or axes and filters applied to a chart.

Refer to:

Calculated Columns and Measures in DAX - SQLBI

Here are the steps you can follow：

1. Create measure.

Measure =
SUMX(FILTER(ALLSELECTED('Table'),'Table'[category]<=MAX('Table'[category])),
[amount])

2. Result:

Column [amount] is set to Sum

Result：

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[sjelting]

p.s: trying to solve it myself, i get very strange behaviour:

Measure =

SUMX(FILTER(ALLSELECTED(q[amount]),'q'[amount]<=MAX('q'[amount])),

[amount])

leads to:

why?

[sjelting]

Thanks, this breaks if I change the sort order to "Sum of amount". Is there a way to set the order of the rows for the calculation? Example sort, desired output in orange:

[FreemanZ]

hi @sjelting 

try to

1) create two measures like this:

AmountAfterDay1 = 
CALCULATE(
    SUM(data[amount]),
    data[date]>=1
)
accumulative_amout = 
VAR _category = MAX(data[category])
RETURN
CALCULATE(
    [AmountAfterDay1],
    data[category]<=_category
)

2) plot a table visual with the two measures and the category column. 

I tried and it worked like this:

[sjelting]

Hello, thanks, this is getting me closer. How can i change the sort order of the calculation? This does the sum how it is sorted by category, but i want to order it by amount. Like this:

how can this be achieved?

[amitchandak]

@sjelting , Try like

New measure =
var _amt = calculate(sum(Table[amount]), filter(Table, Table[Date] >=0))
return
calculate(_amt, filter(allselected(Table[category]) ,Table[category] <= max(Table[category])))

or

New measure =
var _amt = calculate(sum(Table[amount]), filter(Table, Table[Date] >=0))
return
calculate(_amt, filter(allselected(Table) ,Table[category] <= max(Table[category])))

Or try Window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc