Showing results for 
Search instead for 
Did you mean: 

Cumulative sum of multiple groups



I am trying to calculate the cumulative sum per PERIOD_NAME and the sumulative sum per MFG_ORDER_NAME and PERIOD_NAME. Data I have is MFG_ORDER_NAME, PERIOD_NAME and TOTAL_COSTS_PER_JOB_IN_PERIOD. See table. The last column is the value I want to calculate if the cumulative costs per MFG_ORDER_NAME are calculated per period.


Can anyone advise which DAX formula(s) this can solve?


Thanks in advance.


Microsoft Excel - OHW_data.png

Community Support Team
Community Support Team

Re: Cumulative sum of multiple groups

Hi @Hyperchef1969,

Based on my test, you could refer to below steps:

Add index column for your data in query editor:


And create below calculated column:

Cumulative sum = CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Index]<=EARLIER(Table1[Index])&&'Table1'[MFG_ORDER_NAME]=EARLIER(Table1[MFG_ORDER_NAME])))



You could also download the pbix file to have a view.



Daniel He


Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Cumulative sum of multiple groups



Unfortunatel I  a working in DirectQuery mode, so insert an index is not supported within direct query.


However I managed to calculate the **bleep**.costs per MFG_ORDER_NAME by following calculation (Period_CHAR recalculated to a date field called PERIOD_MID_DATE)



Printscreen of solution:

OHW_PowerBI - Power BI Desktop_2019-01-31_15-41-29.png


Question is the following: When I visualize the total costs of PERIOD_NAME_CHAR, so MFG_ORDER_NAME is not included, the total does not match. Total for December 2018 should be 7789542 (export from Power BI to excel - second printscreen below) while total is represented in Power BI as 8321509. See first printscreen. Why is there a difference and how to solve this?


OHW_PowerBI - Power BI Desktop_2019-01-31_16-06-38.png

Microsoft Excel - OHW.png






Re: Cumulative sum of multiple groups

Hi, can someone help please!!! Let me know if something is not clear.


Thanks in advance