cumulative sum calculation

[gelsonwj]

I want to do an accumulated rainfall calculation where the result looks like this.

this is the table. Each farm has its ID and a farm is composed of N rain gauges. In order to calculate the accumulated rain, it is necessary to make an average of each day, of all the rain gauges of a farm X, and day N is added to day N+1... "quantity" is the amount of rain in mm.

Does anyone know how to do this?

[Anonymous · Accepted solution]

Hi @gelsonwj ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2) We can create a measure. 

Measure = 

var _a = SUMMARIZE(FILTER(ALL('Table'),[date]<=MAX('Table'[date])&&[farm_id]=MAX('Table'[farm_id])),'Table'[date],'Table'[farm_id],"Average",CALCULATE(AVERAGE('Table'[quantity]),FILTER(ALL('Table'),[date]=EARLIER('Table'[date])&&[farm_id]=EARLIER('Table'[farm_id]))))

return SUMX(_a,[Average])

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[amitchandak]

@gelsonwj , You can have a measure like example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Or use Window function, link given below

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc