cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## 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 12
Highlighted
Super User II

## Re: Create a formula for cumulative total

1. can you share data via links below so we can use it? Cannot use PNG images.
2. What is the math for the Cumulative Linear?

How to provide sample data in the Power BI Forum

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Regular Visitor

## Re: 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

Highlighted
Super User II

## Re: Create a formula for cumulative total

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``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Regular Visitor

## Re: Create a formula for cumulative total

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!

Highlighted
Super User II

## Re: Create a formula for cumulative total

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Community Support

## Re: Create a formula for cumulative total

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

Community Support Team _ Xiaoxin
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Super User IV

## Re: Create a formula for cumulative total

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Regular Visitor

## Re: Create a formula for cumulative total

Thanks Ashish, but I cannot open the enclosure.  We are working off an older version of Power BI.

Highlighted
Super User II

## Re: Create a formula for cumulative total

@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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors