Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
Solved! Go to Solution.
Hi @guilledo24
I think this calculated column gets pretty close
Total = CALCULATE( SUM('Table1'[Sales]), FILTER( 'Table1', 'Table1'[ID]= EARLIER(Table1[ID]) ) )
Can you explain me all the formula?
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
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
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |