topic Re: Cumulative Sum over grouped by Column in DAX Commands and Tips

Cumulative Sum over grouped by Column

sjelting — Tue, 27 Dec 2022 14:09:15 GMT

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?

Re: Cumulative Sum over grouped by Column

amitchandak — Tue, 27 Dec 2022 14:28:38 GMT

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

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

Re: Cumulative Sum over grouped by Column

FreemanZ — Tue, 27 Dec 2022 14:55:47 GMT

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:

Re: Cumulative Sum over grouped by Column

Anonymous — Wed, 28 Dec 2022 06:57:37 GMT

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

Re: Cumulative Sum over grouped by Column

sjelting — Wed, 28 Dec 2022 07:04:51 GMT

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?

Re: Cumulative Sum over grouped by Column

sjelting — Wed, 28 Dec 2022 07:14:42 GMT

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:

Re: Cumulative Sum over grouped by Column

sjelting — Wed, 28 Dec 2022 07:22:39 GMT

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?

Re: Cumulative Sum over grouped by Column

tamerj1 — Wed, 28 Dec 2022 09:09:09 GMT

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