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 ...
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

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