I need to create a column of running totals based on a previous column. However, the previous column contains multiple iterations of one value. I need to be able to add my next value to just one unique value (not all iterations of it). Please see the referenced Excel screenshot for a better explanation. Please notice that the values will need to keep "Project" and "Sprint" in mind as filter criteria.
Thank you!
Assuming that Sprint column data type is Whole Number, you can use SUMX.
Cumulative Points =
VAR SelectedProject = [Project]
VAR SelectedSprint = [Sprint]
VAR FilteredTable =
FILTER ( 'Table', [Project] = SelectedProject && [Sprint] <= SelectedSprint )
VAR Result =
SUMX (
SUMMARIZE ( FilteredTable, 'Table'[Project], 'Table'[Sprint], 'Table'[Points] ),
[Points]
)
RETURN
Result
Table| Project | Sprint | Points | Cumulative Points |
|---|---|---|---|
| A | 1 | 30 | 30 |
| A | 1 | 30 | 30 |
| A | 2 | 40 | 70 |
| A | 2 | 40 | 70 |
| A | 3 | 25 | 95 |
| A | 3 | 25 | 95 |
| A | 3 | 25 | 95 |
| B | 1 | 10 | 10 |
| B | 1 | 10 | 10 |
| B | 2 | 20 | 30 |
| B | 2 | 20 | 30 |