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 ...
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:

     

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:

     

    • kadapavel's avatar
      kadapavel
      Helper II
      7 years ago

      Thank you very much! Great solution!