Forum Discussion

JLambs20's avatar
JLambs20
Helper III
5 years ago
Solved

Create a New Cumulative Column using DISTINCT

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!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      JLambs20
      Helper III
      5 years ago

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

      \

       

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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