Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Hi,
You may download my solution file from here.
Hope this helps.
Thanks Ashish, but I cannot open the enclosure. We are working off an older version of Power BI.
What problem do you face?
Thank you all for your suggestions. The problem still exists when moving the formula over to the real data. The starting number for week one is not 1/13 of the total for 13 weeks. I think it is because the real data has more than 1 quarter of data.
I would like to share the real data, but it is confidential and I cannot share outside of my organization.
Again thanks for your assistance.
Share some sample data with more than 1 quarter....
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 Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHow 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 Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHere 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
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |