Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 c...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

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])))+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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  Anonymous 

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

     

    Regards,

    Lin