Cumulative sum based on text

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[mahoneypat]

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