Total rows into a column using formula

[Georgesunil77]

Hi Team,

I have two columns :

Columns 1 is Week (Wk1, Wk2, Wk3, Wk4, Wk5)
Columns 2 has totals (100, 200, 400, 500, 100)

I need to create columns for each Week, eg. Wk1 will the third columns which will be based on Column 1, totalling Column  2.

Regards,

Sunil

[v-mengzhu-msft · Accepted solution]

Hi @Georgesunil77 ,

Is this result you want to achieve?

If it is, you can consider adopting my approach.

1.This is an initial table with the same data as yours:

2.I copied the two columns:

3.Select the two duplicate columns, and then click "pivot column" at the top.

4.Finally, I get this table as the result shows.

I hope my process will be helpful to you.

Best regards,

Community Support Team Selina zhu

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

[tamerj1]

Hi @Georgesunil77 
Are you trying to create a visual on power bi or you want to transform the row data? If you you want to create a visual just use a matrix and place Column1 in the columns of the matrix and column2 in the values. If you want to transform the row data you can use power query to Pivot Column1.

[amitchandak]

@Georgesunil77 , If the week name is not is sortable form then create a new column

Week Num = right([Column 1], len([Column 1]) -2)

then create a new column=

sumx(filter(Table, Table[week num] < = earlier(Table[week Num]) ) , [Column 2])