Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional sum based on next row

Hi, I have a requirement to calculate the total for the current row based on the next rows using DAX :   Data Set : Type Value Rule Expected Result A 10 B + C +D +E 250 B 20 C + ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , change like

    new column = if( table[index] < max(table[Index]), sumx(filter(Table, [index] > earlier([index]) ) , [Value]) ,table[Index])

     

    Sorry, I did not notice that

Anonymous's avatar
Anonymous
Not applicable
4 years ago

both the columns are not incremental , column (Type) is a phase & it has to be in sequence . 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , Better to an index column in power query and try, the last formula. I shared a link for index column, if needed

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I did add the index column , its working fine till the 2nd last row , I need to repeat the last row value as is , how I can I do that ? 

     

    I used the below formula :

    new column = sumx(filter(Table, [index] > earlier([index]) , [Value])

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      4 years ago

      Anonymous , change like

      new column = if( table[index] < max(table[Index]), sumx(filter(Table, [index] > earlier([index]) ) , [Value]) ,table[Index])

       

      Sorry, I did not notice that

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        yep its wokring fine , thanks alot .

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    Its working now for all scenario's , i've added IF condtion on top your formula:
    =
    IF (
    MAX(Sheet1[Index]) = Sheet1[Index],
    Sheet1[TotalCount],
    SUMX(FILTER(Sheet1,Sheet1[Index] > EARLIER(Sheet1[Index])),Sheet1[TotalCount]
    )
    )