Forum Discussion

v-amarsh's avatar
v-amarsh
Microsoft Employee
6 years ago

Cumulative sum

Hi Community, I am trying to get a running total on a field based on condition on a different column. Below is my input table along with expected output column (Cumulative Assigned)to produce . Col...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI v-amarsh,

SUMMARIZECOLUMNS function will return a table, you can't directly use it in a calculated column. I'd like to suggest you aggregate with the result table(Greg_Deckler's suggestion) or create a new calculated table with your formulas. (notice: please add 'allselected' functions to ignore current category group or formula will only calculate on row contents of current category group)

Cumulative Table=
SUMMARIZE (
    'Role Master',
    'Role Master'[GD SI Role],
    'Role Master'[Resource Plan],
    'Role Master'[GSI Name],
    'Role Master'[Fiscal Year],
    'Role Master'[Month],
    'Role Master'[Forecast Resource Plan],
    'Role Master'[Assigned],
    'Role Master'[Date],
    "Running Cumulative Assigned", CALCULATE (
        SUM ( 'Role Master'[Assigned] ),
        FILTER (
            ALLSELECTED ( 'Role Master' ),
            'Role Master'[Resource Plan] <= EARLIER ( 'Role Master'[Resource Plan] )
        ),
        VALUES ( 'Role Master'[Assigned] )
    )
)

Regards,

Xiaoxin Sheng