Cumulative sum calculated field

[claudirnava]

Hello

I want to create a cumulative sum but the ammount that I´m intended to use is a calculated field. I´m not wokinr with date because I just want to sum the ammounts once they are sorted in descendent order. "Participación Total" is the field used to sort the data, and I want to create a cumulative sum for the field "MAT" all the date are calcualted fields.

@amitchandak could you help me?

[claudirnava]

Hello Yilong

The problem that I have is that the measure [MAT] is a calculated field is not part of the table, so I tried to calculate a cummulative sum of a calculated field. This is the expression that I used to calculated the MAT:

MAT = CALCULATE(

    SUM( 'Consumos Onco'[Measure Values] ),

    DATESINPERIOD( calendario[Date] , MAX( calendario[Date] ) , -12 , MONTH )

)

And I want to crate the cummulate sum of "Participacion Total" this is the expression:

Participacion Total = DIVIDE([MAT],

    CALCULATE (

        SUM ( 'Consumos Onco'[Measure Values] ),

        FILTER ( ALL ( 'Consumos Onco' ), [Measure Names] = "Consumo" ),DATESINPERIOD( calendario[Date] , MAX( calendario[Date] ) , -12 , MONTH )

    ))

The proble is that I dont know how to calcualte a cummulative sum of a calculated field

[Anonymous]

Hi @claudirnava ,

Maybe I think you can try this DAX code.

Cumulative MAT = 
CALCULATE(
    SUM('YourTableName'[MAT]),
    FILTER(
        ALLSELECTED('YourTableName'),
        'YourTableName'[Participación Total] >= MIN('YourTableName'[Participación Total])
    )
)

This measure will calculate the running total of “MAT” for each row, considering all previous rows with a higher or equal value of “Participación Total”.

If it does not help you, you can provide more details and .pbix file for me.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[claudirnava]

@Anonymous 

Hello Yilong

The problem that I have is that the measure [MAT] is a calculated field is not part of the table, so I tried to calculate a cummulative sum of a calculated field. This is the expression that I used to calculated the MAT:

MAT = CALCULATE(

    SUM( 'Consumos Onco'[Measure Values] ),

    DATESINPERIOD( calendario[Date] , MAX( calendario[Date] ) , -12 , MONTH )

)

And I want to crate the cummulate sum of "Participacion Total" this is the expression:

Participacion Total = DIVIDE([MAT],

    CALCULATE (

        SUM ( 'Consumos Onco'[Measure Values] ),

        FILTER ( ALL ( 'Consumos Onco' ), [Measure Names] = "Consumo" ),DATESINPERIOD( calendario[Date] , MAX( calendario[Date] ) , -12 , MONTH )

    ))

The proble is that I dont know how to calcualte a cummulative sum of a calculated field

[Anonymous]

Hi @claudirnava ,

I think you can change your DAX code.

Cumulative Participacion Total = 
CALCULATE(
    SUMX(
        FILTER(
            ALLSELECTED(calendario[Date]),
            calendario[Date] <= MAX(calendario[Date])
        ),
        [Participacion Total]
    )
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.