Cumulative sum of a measure

[Anonymous]

I have a measure that calc. the difference between 2 columns 

Delta= ABS([CreatedCases]-[ClosedCases])

now i would like to sum the delta column

is it really not possible to create a cumulative sum of a measure?? 

[Sean · Accepted solution]

@Anonymous

How about something like this?

Delta Running Total MEASURE = 
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Table',
            'Table'[Index],
            "ABS to SUM", ABS ( SUM ( 'Table'[CRs Raised] ) - SUM ( 'Table'[CRs Closed] ) )
        ),
        [ABS to SUM]
    ),
    FILTER ( ALL ( 'Table' ), 'Table'[Index] <= MAX ( 'Table'[Index] ) )
)

Good Luck!

[Sean]

@Anonymous

Your Measure calculates (in the Total Row) the absolute value of the difference of the Totals of both columns

Use an iterator instead - SUMX in this case

This way you will get the absolute value on each row and then the sum of all results in the total row

Delta = SUMX ( 'Table', ABS ( 'Table'[CRs Closed] - 'Table'[CRs Raised] ) )

Hope this helps!

[Anonymous]

no unfortunatly , this is what i get - see the last column

the result should be 21 and then 48 etc. 

[Ashish_Mathur · Accepted solution]

Hi @Anonymous,

Try this calculated field formula

=CALCULATE(SUMX(SUMMARIZE('Calendar','Calendar'[Month],"ABCD",ABS([CreatedCases]-[ClosedCases])),[ABCD]),DATESYTD('Calendar'[Date],"31/12"))

Download the file from here.

[Anonymous]

And this works as well , thanks

[Interkoubess]

Hi @Anonymous,

Do you have in your data a columns like Index or date.

I can use it to create the running sum.

Please let us know.

Ninter.

[Anonymous]

yes i have a date column

[Interkoubess]

Hi @Anonymous,

So please give us a sample data with the date and the other column and then I will compute the measure or column.

Thank you.

Ninter

[Anonymous]

You can find the pbix file here https://www.dropbox.com/s/i9evdplpyd6rjfs/PBI%20TEST.pbix?dl=0

[Sean]

@Anonymous

Place Date from your Calendar in the Rows (keep only Year and Month from the resulting Hierarchy)

Delta Running Total MEASURE =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Calendar',
            'Calendar'[YearMonth],
            "ABS to SUM", ABS ( [CreatedCases] - [ClosedCases] )
        ),
        [ABS to SUM]
    ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

Hope this works and helps!

[Anonymous]

thanks a lot this works

[Ashish_Mathur]

You are welcome.

[Sean · Accepted solution]

@Anonymous

How about something like this?

Delta Running Total MEASURE = 
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Table',
            'Table'[Index],
            "ABS to SUM", ABS ( SUM ( 'Table'[CRs Raised] ) - SUM ( 'Table'[CRs Closed] ) )
        ),
        [ABS to SUM]
    ),
    FILTER ( ALL ( 'Table' ), 'Table'[Index] <= MAX ( 'Table'[Index] ) )
)

Good Luck!

[Anonymous]

Nope doesn't work either The problem is that CR raised and CR Closed are both measures CR Closed= Calculate([CountCases];CR[Type] ="Case Closed date") CR Raised = Calculate([CountCases];CR[Type] ="Created on") and apparently it is not possible to sum on a measure - as far as i can understand