Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 irrespecti...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
mahoneypat
Microsoft Employee
5 years ago

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