Forum Discussion
New column with total by id
Hi,
I have the next table
ID | SALES
1 | 123
2 | 50
1 | 08
3 | 26
3 | 74
2 | 12
1 | 59
And i need to create a new column to have the total sales by id, repeat the total in every id:
id | SALES | TOTAL
1 | 120 | 810
2 | 500 | 620
1 | 100 | 810
3 | 260 | 1000
3 | 740 | 1000
2 | 120 | 620
1 | 590 | 810
3 | 590 | 1000
Thanks,
Hi guilledo24
I think this calculated column gets pretty close
Total = CALCULATE( SUM('Table1'[Sales]), FILTER( 'Table1', 'Table1'[ID]= EARLIER(Table1[ID]) ) )
4 Replies
- Phil_Seamark
Microsoft Employee
Hi guilledo24
I think this calculated column gets pretty close
Total = CALCULATE( SUM('Table1'[Sales]), FILTER( 'Table1', 'Table1'[ID]= EARLIER(Table1[ID]) ) )Phil_Seamark thanks for answering, that solution it's excelent.
Now I need the same result but the total sales by id, month and year, can you give me the best solution?
id | SALES | MONTH | YEAR
1 | 120 | 2 | 2018
2 | 500 | 6 | 2017
1 | 100 | 3 | 2018
3 | 260 | 3 | 2017
3 | 740 | 2 | 2018
2 | 120 | 2 | 2017
1 | 590 | 6 | 2017
3 | 590 | 2 | 2018
v-juanli-msftCommunity Support
Hi guilledo24
Phil_Seamark's suggestion should be right,however, as i tested, there is something different from your example, meanwhile, I see your example may take a little mistake.
If you have any question, please let me know.
Best Regards
Maggie
Can you explain me all the formula?
