Forum Discussion

kadapavel's avatar
kadapavel
Helper II
7 years ago
Solved

sum rows based on condition

Hi, can someone help me to make sum calculation of each heat up sequence, shown on exapmle. In excel I can make reference to previous cell of same column I calculate, here I managed only to refer to earlier values in another columns, but that does not help. 

Other option I considered is to name each heat up secuence and then i could use something like this 

=CALCULATE(SUM([PrevTemp]), 
           FILTER(ALL(‘Table’), 
             ‘Table’[NameOfHeatUpSeq]=EARLIER(‘Table’[NameOfHeatUpSeq])
                 )
          )

But was not able to give new name to each sequence...

Thanks in Advance

Need Help
  • ofirk's avatar
    ofirk
    7 years ago

    Hi kadapavel,

     

    Sorry I missed that.

    Can you try this:

     

    1. Create a column that saves the index of the last row before heating begins:

     

    Heat Start = 
IF([Prev Temp]>0, 
    MAXX(
        FILTER(Table2, 
                [Index] < EARLIER([Index]) && 
                [Prev Temp] = 0), 
        [Index]))

     

    2. Use the following calculation:

    Sum of Each Heat Up = 
IF([Prev Temp] > 0, 
    SUMX(
        FILTER(Table2, 
                [Index] <= EARLIER([Index]) && 
                [Index] >= EARLIER([Heat Start])), 
        [Prev Temp]))

    I believe it works:

     

4 Replies

  • ofirk's avatar
    ofirk
    Resolver II
    7 years ago

    Hi kadapavel,

     

    I think you could use the following column (with your table instead of Table1):

     

    Sum of Each Heat Up = SUMX(FILTER(Table1, [index] <= EARLIER([index])), [Prev Temp])

     

    These are the results I get when I use some of your data:

     

     

    If you don't want to use the index column, you can probably use your timestamp column.

    • kadapavel's avatar
      kadapavel
      Helper II
      7 years ago

      Hi, thanks for reply, It almost worked. I need to separate sum calculation for each sequence. Target is to calculate cumulative of each heating.

      I 

      I made new column and modified your DAX a bit, but still there is some issue...

      Testsc = 
var Blank1 = calculate(max('rec METEO'[Prev Temp]), FILTER('rec METEO', 'rec METEO'[Prev Temp]), 'rec METEO'[Index]=earlier('rec METEO'[Index])-1)
return
if(ISBLANK('rec METEO'[Prev Temp]),BLANK(),
    if(ISBLANK(Blank1),'rec METEO'[Prev Temp],
                        SUMX(FILTER('rec METEO', [index] <= EARLIER([index])), [Prev Temp])) )
      • ofirk's avatar
        ofirk
        Resolver II
        7 years ago

        Hi kadapavel,

         

        Sorry I missed that.

        Can you try this:

         

        1. Create a column that saves the index of the last row before heating begins:

         

        Heat Start = 
IF([Prev Temp]>0, 
    MAXX(
        FILTER(Table2, 
                [Index] < EARLIER([Index]) && 
                [Prev Temp] = 0), 
        [Index]))

         

        2. Use the following calculation:

        Sum of Each Heat Up = 
IF([Prev Temp] > 0, 
    SUMX(
        FILTER(Table2, 
                [Index] <= EARLIER([Index]) && 
                [Index] >= EARLIER([Heat Start])), 
        [Prev Temp]))

        I believe it works: