Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kadapavel
Helper II
Helper II

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

Capture.PNG

Thanks in Advance

1 ACCEPTED SOLUTION

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:

 

examp.PNG

View solution in original post

4 REPLIES 4
ofirk
Resolver II
Resolver II

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:

 

example_.PNG

 

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

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

Capturex.PNG

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:

 

examp.PNG

Thank you very much! Great solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.