Forum Discussion

claudirnava's avatar
claudirnava
Frequent Visitor
2 years ago

Cumulative sum calculated field

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?
 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      claudirnava
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.

  • claudirnava's avatar
    claudirnava
    Frequent Visitor
    2 years ago

    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