Forum Discussion
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 ...
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:
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])) )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:
Thank you very much! Great solution!