Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Conditional Summing based on the last time one value occured

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

 

Need Help

4 Replies

  • Mohammad_Refaei's avatar
    Mohammad_Refaei
    Icon for Solution Specialist rankSolution Specialist
    5 years ago

    Try this:

    Time =
CALCULATE (
    SUMX ( DATA, DATA[Duration (Hour)] ),
    DATA[Condition] = "Process Time",
    FILTER ( ALL ( DATA ), DATA[Index] <= [Maximum index] )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Mohammad_Refaei
        Icon for Solution Specialist rankSolution Specialist
        5 years ago

        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.