cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
garynorcross Regular Visitor
Regular Visitor

Calculating a Cumulative Sum in Power BI

Hi everyone,

 

I need some help putting together a calculated column or measure for a Net Requirements report in Power BI.  

Here is a sample of my data:
Capture.JPG

 

What I need is a a cumulative sum for the Requirement Quantity for each line.  The first line's value should be 615.00, then 565.00, then 585.00 and so on.  There are changes by the 'Number' field and by the 'Requirement Date' field.  Anyone have any idea how to create this?

 

Thanks!

4 REPLIES 4
Super User
Super User

Re: Calculating a Cumulative Sum in Power BI

I would recommend implementing an Index column in Power Query. The solution should then be trivial for either a column or measure formula.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

gckcmc Member
Member

Re: Calculating a Cumulative Sum in Power BI

A running total of inventory on hand is effectively what I interpret you to mean you need.  In this table, you're describing things by date.  Without a time stamp, you could have several ways this gets interpreted.  for example:

 

615

-50

+20

+10

+10

+10

which is effectively in the order you have the sample listed.  However, you could just as easily have:

615

+20

+10

+10

-50

as all the +/- are happening on the same day.

 

So since the query is going to really not have any row level associations, you have to determine if your input data is in sequential order on purpose, and if that is the case, then I would build a solution that adds in a column which is simply a sequential number counter based on the row.  This is done in lots of cases as an "ID" type column.  Then when you build out the chart, you can have the matrix, or graph or whatever, sort by the "ID" column and you preserve order of the data you loaded.

 

If order of data isn't from what you loaded, then I would go by date column (adding time stamp if you want your inbound to get added to your running inventory total before outbound gets deducted, etc.)

 

hope this helps.

garynorcross Regular Visitor
Regular Visitor

Re: Calculating a Cumulative Sum in Power BI

Thanks for the response - I've created the Index Column in Power Query, is there a function I can use for the Cumulative sum in Power Query or should it be done as a calculated column in DAX?

gckcmc Member
Member

Re: Calculating a Cumulative Sum in Power BI

I guess it depends on what you're trying to achieve.  For example if you want your visualization to be a matrix or chart, then you can simply use the index to arrange the order of the inventory column.  

 

A calculated cumulative (measure/function) I'm not sure makes sense given your scenario...i.e. when do you decide to "stop" the cumulative count?  are you going to specify and index?  a date? or no stop at all, and just a sum of the entire inventory running total, which gets you a net sum at the end of your data?

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)