Forum Discussion

rjeffers's avatar
rjeffers
New Member
8 years ago
Solved

Running Sum

Hello all,

 

Hoping someone can help me out with this.

I've created a measure called 'Delta. Calculated as

DELTA = SUM(TABLE[WARRANTYUNITS]) - CALCULATE([AVG.WARR.]),ALL(TABLE))

 

AVG.WARR. is another measure calculated as

AVG.WARR = SUM(TABLE[WARRANTYUNITS]) / DISTINCTCOUNT(TABLE[MONTH))

 

I don't need a total sum of Delta, I need to add the result for Month 2 to Month 1, Month 3 to this and so on

The other picture is the table in excel using the correct calculation

 

I'm hoping to avoid linking the Delta by Month output into a new table so as to avoid refreshing the data continuously

Is there perhaps something I can do with time intelligence to make this calculate correctly?

 

 

 

 

 

 

 

 

 

 

 

 

Thank you for any help that may be provided!

  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi rjeffers,

     

    You can try to use below mesure to calcualte running total:

    CUSUM =
    VAR AVG =
        SUMX ( ALL ( TABLE ), [WARRANTYUNITS] ) / 12
    RETURN
        SUMX (
            FILTER ( ALLSELECTED ( TABLE ), [MONTH] <= MAX ( [MONTH] ) ),
            [WARRANTYUNITS]
        )
            - AVG * COUNTROWS ( VALUES ( TABLE[MONTH] ) )
    

     

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi rjeffers,

     

    You can try to use below mesure to calcualte running total:

    CUSUM =
    VAR AVG =
        SUMX ( ALL ( TABLE ), [WARRANTYUNITS] ) / 12
    RETURN
        SUMX (
            FILTER ( ALLSELECTED ( TABLE ), [MONTH] <= MAX ( [MONTH] ) ),
            [WARRANTYUNITS]
        )
            - AVG * COUNTROWS ( VALUES ( TABLE[MONTH] ) )
    

     

    Regards,

    Xiaoxin Sheng