SUM function

[Alex_0201]

Hi, I'm confused by SUM/SUMX formulas. I want to create a column called Total Time that:

- has 0 in the first row

- starting from the second row down every cell is a sum of a current value from a column called time and all the values from the rows above.

Easily done in Excel (=A2+B2), but can get it right in powerbi.

[v-lili6-msft]

hi @Alex_0201 

For your case, you need to add an index column first, then create a new column as below:

Total time, hrs = CALCULATE(SUM('Table'[time, hrs]),FILTER('Table','Table'[Index]<EARLIER('Table'[Index])))+0

Result:

And if you want show 191, that needs you create a measure to show it in visual, try this way as below:

Step1:

Add  Index table as below:

Index table = GENERATESERIES(1,MAX('Table'[Index])+1,1)

Step2:

Create a relationship by index column as below:

Step3:

Create a measure as below:

Total time, hrs = CALCULATE(SUM('Table'[time, hrs]),FILTER(ALL('Index table'),'Index table'[Index]<MAX('Index table'[Index])))+0

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Alex_0201]

Hi @v-lili6-msft thanks for that! 

I've forgot to add a column called step_no. 

There is one problem, this measure adds new rows:

Original table visualization (target_duration is the time column in my post):

After adding the measure:

[v-lili6-msft]

hi  @Alex_0201 

Could you please share your sample pbix file for us have a test? I will help you adjust it quickly.

Regards,

Lin

[amitchandak]

@Alex_0201 , You have add an index column

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Cumm = CALCULATE(SUM(Table[time hrs]),filter(all(Table),Table[index] <=maxx(Table,Table[index])))

[Alex_0201]

Hi @amitchandak  thanks for that! 

I've forgot to add a column called step_no. 

There is one problem, this measure/column adds new rows:

Original table visualization (target_duration is the time column in my post):

After adding the measure:

[amitchandak]

@Alex_0201 , Just check in visual table, does target duration has an aggregation, seem like do not summarize option on that. If so make it Sum.

[Alex_0201]

@amitchandak changed it to Sum. The same result. 

[Alex_0201]

@amitchandak can I create an index column that copies the column step no? If i just add the index column (from 1), the order is different from the order in step no

[FrankAT]

Hi @Alex_0201,

you are looking for the running total. You can accomplish it as follows (see figure):

Regards FrankAT

[Alex_0201]

@FrankAT thank you! but is there a formula that can both add a row with 0 and then the running total, so it'd look like on the screenshot?

[parry2k]

@Alex_0201 now for running total, you have to have a column that defines the order in which you want to sum this running total, that column can be a date or index columns. Do you have something like that in your model? if not then add an index column in power query and then running total can be created.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Alex_0201]

@parry2k yes, I do. it's called Step No: