CODEWITHSUNDEEP
powerbidax
Josh Lambert
Josh Lambert

Reputation: 47

Create a New Cumulative Column using DISTINCT

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.

enter image description here

Thank you!

Upvotes: 2

Views: 35

Answers (1)

Angelo Canepa
Angelo Canepa

Reputation: 1781

Assuming that Sprint column data type is Whole Number, you can use SUMX.

Calculation: Calculated Column

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

Output: 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

Upvotes: 2

Related Questions