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!
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...
This 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?
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.
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] ) ) )
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.