Forum Discussion
Helper Icumulative sum calculation
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?
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.
2 Replies
- amitchandak
Super User
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
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.
