Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
gckcmc
Resolver I
Resolver I

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.

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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?

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.