Conditional Summing based on the last time one value occured

[Anonymous]

Dear All,

I am trying to sum one of the item in a specific column after another condition Occured.

For example, below is my sample data set

I would like to calculate all the process time for every condition after that condition occured. Below is my expected 

So far what we've done is to create maximum index for every condition and apply this calculation. Below is the measure of maximum index and the formula:

Formula:

Time = CALCULATE(sumx(DATA,DATA[Duration (Hour)]),DATA[Condition]="Process Time",DATA[Index] < [Maximum index])

But it returns this failure

.

I am not sure what's wrong. Any advice will be appreciated.

Best Regards,
Handy

[Mohammad_Refaei]

Try this:

Time =
CALCULATE (
    SUMX ( DATA, DATA[Duration (Hour)] ),
    DATA[Condition] = "Process Time",
    FILTER ( ALL ( DATA ), DATA[Index] <= [Maximum index] )
)

[Anonymous]

Hi, since I want to calculate the process time after the Maximum Index of each conditions (or remarks) i think the condition for the index should be > max index not <= max index. Fix this code but now it will return 0 value. 

So if input < in the formula, it will calculate all process time, and if I input > in the formula it will not calculate any process time 

the revised code is 

Time = 
CALCULATE (
    SUMX ( DATA, DATA[Duration (Hour)] ),
    DATA[Remarks] = "Process Time",
    FILTER ( ALLSELECTED(DATA), DATA[Index] > [Maximum index] )
)

I suspect that the maximum index in that code is refer to the process time itself since it also has index number. So when I choose < it will calculate all the process time since all process time will have index <= maximum index of process time and when I choose > it will calculate no process time since no process time will have index greater than the maximum index of the table itself.  I hope it is clear.

So basically I need a function to specifiy the max index for each unique column not max index of the table or the process time.

Below is some snapshot.

Any help will be appreciated.

Regards,

Handy Pratama

[Mohammad_Refaei]

I am sorry @Anonymous but I cannot follow. It would help a lot if you can share your model with sample calculation of the output for one record.

[Anonymous]

Hi Refaei,

Thank You for your answer.

Now it give some value but

It return value of sum of all process time in the table. 

i.e. from index number 1 to last index number if its process time it is added.

Do you have any other idea?

Regards,

Handy Pratama