Forum Discussion
SUM function
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.
12 Replies
Anonymous 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.⚡
parry2k yes, I do. it's called Step No:
Hi Anonymous,
you are looking for the running total. You can accomplish it as follows (see figure):
Regards FrankAT
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?
Anonymous , 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])))
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:
Anonymous , Just check in visual table, does target duration has an aggregation, seem like do not summarize option on that. If so make it Sum.
hi Anonymous
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])))+0Result:
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])))+0Result:
and here is sample pbix file, please try it.
Regards,
Lin
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:
hi Anonymous
Could you please share your sample pbix file for us have a test? I will help you adjust it quickly.
Regards,
Lin
