Forum Discussion
Cumulative sum based on text
Here is my table and I have the first three columns and I am trying to calculate the 3rd column which is cumulative sum though i have dates in the bigger table i just want this small table irrespective of the dates based on Type.'
type total cumsum
abc 32 32
def 33 65
ghi 34 99
Hi Anonymous,
Here are two ways to provide you：
一.
1.Power Query generate index column.
2.Create a measure.
Measure = CALCULATE(SUM('Table'[total]),FILTER(ALLSELECTED('Table'),'Table'[Index]<=MAX('Table'[Index])))3.Result
二.
1.Create a calculated column.
Column = CALCULATE(SUM('Table'[total]),FILTER(ALLSELECTED('Table'),'Table'[type]<=EARLIER('Table'[type])))2.Result.
You can downloaded PBIX file from here.
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.
2 Replies
Hi Anonymous,
Here are two ways to provide you：
一.
1.Power Query generate index column.
2.Create a measure.
Measure = CALCULATE(SUM('Table'[total]),FILTER(ALLSELECTED('Table'),'Table'[Index]<=MAX('Table'[Index])))3.Result
二.
1.Create a calculated column.
Column = CALCULATE(SUM('Table'[total]),FILTER(ALLSELECTED('Table'),'Table'[type]<=EARLIER('Table'[type])))2.Result.
You can downloaded PBIX file from here.
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.
To do a cumulative sum you will need a column that changes over time, like a Date column or an index column. If you don't have one, you can add an Index column in the query editor on the Add column tab. You can then use a measure expression like this to get cumulative sum.
CumulativeSum = var thisindex = Table[Index] // or use your Date column in a similar way
return CALCULATE(SUM(Table[Total]), all(Table[Index]), Table[Index]<= thisindex)
Regards,
Pat
