Forum Discussion
Cumulative Sum over grouped by Column
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:
7 Replies
- amitchandak
Super User
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
FreemanZhi 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:
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?
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
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:
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?
