Create a New Cumulative Column using DISTINCT

[JLambs20]

Hello!

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!

[v-yiruan-msft · Accepted solution]

Hi @JLambs20 ,

You can create a calculated column or measure as below to achieve it:

1. Calculated column

Column for Cumulative Points = 
VAR _tab =
    SUMMARIZE (
        FILTER (
            ALL ( 'AZUP - Apollo Main Data' ),
            'AZUP - Apollo Main Data'[Project]
                = EARLIER(  'AZUP - Apollo Main Data'[Project] )
                && 'AZUP - Apollo Main Data'[Sprint]
                    <= EARLIER ( 'AZUP - Apollo Main Data'[Sprint] )
        ),
        'AZUP - Apollo Main Data'[Project],
        'AZUP - Apollo Main Data'[Sprint],
        "point", MAX ( 'AZUP - Apollo Main Data'[Points] )
    )
RETURN
    SUMX ( _tab, [point] ) 

2. Measure

Cumulative Points = 
VAR _tab =
    SUMMARIZE (
        FILTER (
            ALL ( 'AZUP - Apollo Main Data' ),
            'AZUP - Apollo Main Data'[Project]
                = SELECTEDVALUE ( 'AZUP - Apollo Main Data'[Project] )
                && 'AZUP - Apollo Main Data'[Sprint]
                    <= SELECTEDVALUE ( 'AZUP - Apollo Main Data'[Sprint] )
        ),
        'AZUP - Apollo Main Data'[Project],
        'AZUP - Apollo Main Data'[Sprint],
        "point", MAX ( 'AZUP - Apollo Main Data'[Points] )
    )
RETURN
    SUMX ( _tab, [point] )

Best Regards

[Ashish_Mathur]

Hi,

Please show the expected result in a simple Table.

[amitchandak]

@JLambs20 , Try a measure like

sumx(addcolumns(summarize(Table, Table[project], Table[sprint], Table[points]), "_1",calculate(sum(Table[points]),filter(allselected(Table), [sprint] <=max([sprint]) && [project] = max([project])))),[_1])

[JLambs20]

Unfortunately, I'm getting an error.  What am I missing?

\

[amitchandak]

@JLambs20 , after the filter getting closed after [sprint]  before <= max is at the wrong place. remove and correct