Calculate sum of column based on specific words in row

[Anonymous]

Hi all , I am new to power bi and I faced a challenge below.

I am required to calculate the output (4th column) based on start key word UDT-Wait(1st column) and end key word SBT(2nd column). The sum has to be reset and calculation needs to be repeated. 

Any idea how I can start?

[Anonymous]

Hi @Anonymous ,

Please first create index column in Power Query Editor.

If the data is regular like in the graph, we can group by the regularity.

Group = INT(('Table'[Index]+2)/3)

sum = 
VAR _SUM =
    CALCULATE (
        SUM ( 'Table'[Column3] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Group] = EARLIER ( 'Table'[Group] ) )
    )
RETURN
    IF ( 'Table'[Column2] = "SBT", _SUM, BLANK () )

If the data is irregular, we need to find another way. Please create a new column.

sum2 = 
VAR _step0 =
    IF ( 'Table'[Column2] = "SBT", 'Table'[Index], BLANK () )
VAR _step1 =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Column1] = "UDT-Wait" ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] < _step0 )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Column3] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] >= _step1 && 'Table'[Index] <= _step0 )
    )

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!