Forum Discussion
Create a formula for cumulative total
Hello,
I am trying to create a measure (or column) that calculates the rolling cumulative sales in a linear format for a table or matrix. I know the sales and create a formula for the Cumulative Sales, but have been unsuccessful in creating a formula for the Cumulative Linear column. Any help would be appreciated. Thanks!
12 Replies
- edhans
Community Champion
- can you share data via links below so we can use it? Cannot use PNG images.
- What is the math for the Cumulative Linear?
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI ForumHere is the data in the correct format (hopefully). As for the formula, I am taking the 13 week total divided by 13 to get the average weekly total. Then week 1 is the average total, week 2 is the average weekly total * 2, week 3 is the average weekly total *3, and so on...
sales
Cumulative Sales
Cumulative Linear
wk 1
5
5
18.6
wk 2
15
20
37.23
wk 3
25
45
55.85
wk 4
6
51
74.46
wk 5
22
73
93.08
wk 6
30
103
111.69
wk 7
21
124
130.31
wk 8
5
129
148.92
wk 9
16
145
167.54
wk 10
23
168
186.15
wk 11
19
187
204.77
wk 12
20
207
223.38
wk 13
35
242
242.00
242
Hi merath01,
Can you please share a pbix file with some dummy data to test? It should help us to clarify your scenario and do test to coding formula on it.
In addition, if edhans's code not works. I'd like to suggest you enter to query editor to extract the week number from your week field, then you can use the new filed as the index to do cumulative calculations.
Cumulative sales = CALCULATE ( SUM ( 'Table'[sales] ), FILTER ( ALLSELECTED ( 'Table' ), [index] <= MAX ( 'Table'[index] ) ) )Regards,
Xiaoxin Sheng
- Ashish_Mathur
Super User
Thanks Ashish, but I cannot open the enclosure. We are working off an older version of Power BI.
- edhans
merath01 any plans to share data that we can work with? My measure works with the data you provided me. I'd share my PBIX file but it is the same as Ashish_Mathur I'm sure, we are both on the latest versions of Power BI.
