topic Re: Cumulative sum in Developer

Cumulative sum

v-amarsh — Wed, 20 May 2020 12:16:21 GMT

Hi Community,

I am trying to get a running total on a field based on condition on a different column.

Below is my input table along with expected output column (Cumulative Assigned)to produce . Column to run a cumulative sum is "Assigned" and Condition is to run cumulative until "Resource Plan" reaches max value for that period. In this case max value ocurs at FY20-P10.

Fiscal Year	GSI Name	GD SI Role	Month	Resource Plan	Assigned	Cumulative Assigned
FY20	X	Offshore	FY20-P08	61	40	40
FY20	X	Onsite	FY20-P08	41	20	20
FY20	X	Offshore	FY20-P09	66	23	63
FY20	X	Onsite	FY20-P09	44	12	32
FY20	X	Offshore	FY20-P10	126	41	104
FY20	X	Onsite	FY20-P10	84	43	75
FY20	Y	Offshore	FY20-P08	67	60	60
FY20	Y	Onsite	FY20-P08	45	40	40
FY20	Y	Offshore	FY20-P09	56	55	115
FY20	Y	Onsite	FY20-P09	33	32	72
FY20	Y	Offshore	FY20-P10	100	76	191
FY20	Y	Onsite	FY20-P10	78	54	126

I have tried Summarize columns function but result is as not as expected.

Cumulative Table Summary =

SUMMARIZECOLUMNS

(

'Role Master'[GD SI Role],

'Role Master'[Resource Plan],

'Role Master'[GSI Name],

'Role Master'[Fiscal Year],

'Role Master'[Month],

'Role Master'[Forecast Resource Plan],

'Role Master'[Assigned],

'Role Master'[Date],

"Running Cumulative Assigned",CALCULATE(

SUM('Role Master'[Assigned]),

FILTER('Role Master','Role Master'[Resource Plan]<=MAX('Role Master'[Resource Plan])),

VALUES('Role Master'[Assigned])

)

Note ; Offshore and onsite values should not be summed up together. (Offshore-->Offshore and Onsite-->Onsite for each period), gorup by each GSI Name

Thanks

Re: Cumulative sum

Greg_Deckler — Wed, 20 May 2020 12:28:48 GMT

I don't see anything where you have a sortable column that would allow you to define "previous". If you had that, you could do something like this:

Column = SUMX( FILTER( 'Table', [GSI Name] = EARLIER('Table'[GSI Name]) && [GD SI Role] = EARLIER([GD SI Role]) && [Some field] <= EARLIER([Some field]) ), [Plan Assigned] )

Re: Cumulative sum

chadrenstrom — Thu, 21 May 2020 21:07:34 GMT

Not an answer, but similar idea to the Pareto chart in Excel. Unfortunately the Pareto chart doesn't exist in Power BI, but there have been a few blog posts around the internet on how to create them. The order changes based on the group total, so depending what you need, adding an index column prior to any calculations may not be the solution you need. Then again, this may be a step further than you're looking for.

Re: Cumulative sum

Anonymous — Fri, 22 May 2020 06:32:48 GMT

HI @v-amarsh,

SUMMARIZECOLUMNS function will return a table, you can't directly use it in a calculated column. I'd like to suggest you aggregate with the result table(Greg_Deckler's suggestion) or create a new calculated table with your formulas. (notice: please add 'allselected' functions to ignore current category group or formula will only calculate on row contents of current category group)

Cumulative Table= SUMMARIZE ( 'Role Master', 'Role Master'[GD SI Role], 'Role Master'[Resource Plan], 'Role Master'[GSI Name], 'Role Master'[Fiscal Year], 'Role Master'[Month], 'Role Master'[Forecast Resource Plan], 'Role Master'[Assigned], 'Role Master'[Date], "Running Cumulative Assigned", CALCULATE ( SUM ( 'Role Master'[Assigned] ), FILTER ( ALLSELECTED ( 'Role Master' ), 'Role Master'[Resource Plan] <= EARLIER ( 'Role Master'[Resource Plan] ) ), VALUES ( 'Role Master'[Assigned] ) ) )

Regards,

Xiaoxin Sheng

Re: Cumulative sum

v-amarsh — Fri, 22 May 2020 07:17:10 GMT

Thanks for your reply chandrenstrom. I have tried using DAX version equivalent for Parento chart in Excel by some internet sources. However, the result is giving me scalar value of the total sum of values .

Thanks

Re: Cumulative sum

Anonymous — Tue, 26 May 2020 07:54:59 GMT

HI @v-amarsh,

If my formula not helpful, can you please share a pbix file with some dummy data and expected results to test?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Re: Cumulative sum

v-amarsh — Tue, 26 May 2020 09:26:29 GMT

Hi @Anonymous

Thanks for your reply. Please find the attached .pbix file. The table on the RHS is the output from the DAX formula.

SUM function used in summarize will always give sum for a group together rather than cumulative progression sum what is expected.

Note : My cumulative sum should stop once it reaches the MAX value found in Resource Plan for that particular period. In this dummy data set, max value is seen for period FY20-P10. This can vary.

I am unable to attach my .pbix here so sending the link for report. Request you to download .pbix from that link.

https://msit.powerbi.com/groups/me/reports/19c5c614-0d1d-49ae-a681-55d54cb16729?ctid=72f988bf-86f1-41af-91ab-2d7cd011db47

Thanks

Re: Cumulative sum

Anonymous — Thu, 28 May 2020 08:07:25 GMT

Hi @v-amarsh,

I can't access your link, can you please upload the sample to 'onedrive for business' and share the link here?
Regards,

Xiaoxin Sheng

Re: Cumulative sum

v-amarsh — Thu, 28 May 2020 08:51:01 GMT

Hi @Anonymous

I have uplaoded the file to One drive for business and shared with you. Below is the link.

https://microsoft-my.sharepoint.com/:u:/p/v-amarsh/EX-TUXMV6PtHhMeFdxGXe_oBue4ky_qToMD9mrrMign9-Q?e=fuT5HT

Thanks

Re: Cumulative sum

Anonymous — Thu, 28 May 2020 09:55:29 GMT

Hi @v-amarsh,

I test the modified formula and it can output the expected result as you shared, please try it if it meets your requirement:

Cumulative Table = SUMMARIZE ( 'Role Master', 'Role Master'[GD SI Role], 'Role Master'[Resource Plan], 'Role Master'[GSI Name], 'Role Master'[Fiscal Year], 'Role Master'[Month], 'Role Master'[Assigned], "MyExpectedOutputColumn", VAR maxMonth = CALCULATE ( MAX ( 'Role Master'[Month] ), FILTER ( ALLSELECTED ( 'Role Master' ), [Assigned] <> BLANK () ), VALUES ( 'Role Master'[Fiscal Year] ), VALUES ( 'Role Master'[GSI Name] ), VALUES ( 'Role Master'[GD SI Role] ) ) RETURN IF ( RIGHT ( [Month], 2 ) < RIGHT ( maxMonth, 2 ), CALCULATE ( SUM ( 'Role Master'[Assigned] ), FILTER ( ALLSELECTED ( 'Role Master' ), RIGHT ( [Month], 2 ) <= RIGHT ( EARLIER ( 'Role Master'[Month] ), 2 ) ), VALUES ( 'Role Master'[Fiscal Year] ), VALUES ( 'Role Master'[GSI Name] ), VALUES ( 'Role Master'[GD SI Role] ) ) ) )

Regards,

Xiaoxin Sheng