Forum Discussion
Create a formula for cumulative total
Here 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 |
Community ChampionThis works. My weeks aren't sorted properly, but you can see weeks 3, 4, 5 match your numbers.
Linear Total =
VAR varAverage =
AVERAGEX(
ALL( 'Table' ),
'Table'[Sales]
)
VAR varCurrentWeek =
MAX( 'Table'[Week] )
VAR varCurrentWeekNo =
VALUE(
RIGHT(
varCurrentWeek,
LEN( varCurrentWeek )
- FIND(
" ",
varCurrentWeek
)
)
)
VAR Result = varAverage * varCurrentWeekNo
RETURN
Result
Thanks for your assistance. I still can't get it to work with my real data, the numbers are too low. I did notice that the total on your example did not equal the 242 total sales, which it should. Any thoughts?
Thanks again!
- edhans
I don't know why it isn't working with your actual numbers. I'd need to see sample data. I don't know what "too low" means.
As to the total, I should have removed that. The total in this matrix is useless as it is an average. But if my data was sorted properly (I didn't bother setting up a sort-by-column setting) it would show 242 for the final week.