Forum Discussion

NKTMS's avatar
NKTMS
Frequent Visitor
3 years ago
Solved

Consecutive sum

Hi,

I've been stuck on this for a while. 
I have this table in PowerBI with Columns Lkey,Mkey and Count. Nothings unique in that. I want to create a new column Sum which sums up the values of count  till a blank value occurs and then it restarts again as showing in the figure. 


I'm a bit confused on the best method to find it. It can be on the same table. Or can we create another table an populate it with the sum with corresponding Lkey and Mkeys?

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , NKTMS 

    Thanks for your sample data and end result first!

    Here are the steps you can refer to :
    (1)My test data is the same as yours.

    (2)We can add an index column in Power Query to judge the row .

    (3)Then we can apply the data to Power BI Desktop, and we can cretae a calculated column.

    I create two calculated columns and you can choose which the column you need to get :

     

    Column 1 = var _pre_blank =  FILTER('Table','Table'[Index]<EARLIER('Table'[Index]) && 'Table'[Count] =  BLANK())
var _blank_index =IF(ISBLANK( MAXX(_pre_blank,[Index])), 0, MAXX(_pre_blank,[Index]))
var _value = SUMX( FILTER('Table' , 'Table'[Index]>_blank_index && 'Table'[Index]<=EARLIER('Table'[Index])) , [Count])
return
_value

     

    Column 2 = var _pre_blank =  FILTER('Table','Table'[Index]<EARLIER('Table'[Index]) && 'Table'[Count] =  BLANK())
var _blank_index =IF(ISBLANK( MAXX(_pre_blank,[Index])), 0, MAXX(_pre_blank,[Index]))
var _value = SUMX( FILTER('Table' , 'Table'[Index]>_blank_index && 'Table'[Index]<=EARLIER('Table'[Index])) , [Count])
var _next_blank =   FILTER('Table','Table'[Index]>=EARLIER('Table'[Index]) && 'Table'[Count] =  BLANK())
var _blank_next=IF( ISBLANK( MINX(_next_blank,[Index]) ) , MAX('Table'[Index])+1 , MINX(_next_blank,[Index]))
return
IF(_blank_next -1 = [Index] , _value, BLANK())

     

    Then we can get the result as follows:

     

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

3 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , NKTMS 

    Thanks for your sample data and end result first!

    Here are the steps you can refer to :
    (1)My test data is the same as yours.

    (2)We can add an index column in Power Query to judge the row .

    (3)Then we can apply the data to Power BI Desktop, and we can cretae a calculated column.

    I create two calculated columns and you can choose which the column you need to get :

     

    Column 1 = var _pre_blank =  FILTER('Table','Table'[Index]<EARLIER('Table'[Index]) && 'Table'[Count] =  BLANK())
var _blank_index =IF(ISBLANK( MAXX(_pre_blank,[Index])), 0, MAXX(_pre_blank,[Index]))
var _value = SUMX( FILTER('Table' , 'Table'[Index]>_blank_index && 'Table'[Index]<=EARLIER('Table'[Index])) , [Count])
return
_value

     

    Column 2 = var _pre_blank =  FILTER('Table','Table'[Index]<EARLIER('Table'[Index]) && 'Table'[Count] =  BLANK())
var _blank_index =IF(ISBLANK( MAXX(_pre_blank,[Index])), 0, MAXX(_pre_blank,[Index]))
var _value = SUMX( FILTER('Table' , 'Table'[Index]>_blank_index && 'Table'[Index]<=EARLIER('Table'[Index])) , [Count])
var _next_blank =   FILTER('Table','Table'[Index]>=EARLIER('Table'[Index]) && 'Table'[Count] =  BLANK())
var _blank_next=IF( ISBLANK( MINX(_next_blank,[Index]) ) , MAX('Table'[Index])+1 , MINX(_next_blank,[Index]))
return
IF(_blank_next -1 = [Index] , _value, BLANK())

     

    Then we can get the result as follows:

     

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

    • NKTMS's avatar
      NKTMS
      Frequent Visitor
      3 years ago

      Hi Thanks for the solution, although i did come across something. It restarts the sum if the value in count column is 0 but I only want it to restart only if it is blank. I hope you can help me with it. 

  • NKTMS's avatar
    NKTMS
    Frequent Visitor
    3 years ago

    Hi, First of all, thanks for the solution. It is working out for me. The only issue is that it takes hours to run this. I optimized the table but still its taking a long time. Any idea on how to optimize the code?
    To make the code run properly i added two conditions, LK=PREVIOUS(LK) and MK=PREVIOUS(MK) in the variables pre_blank, _Value and next_blank.