cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!

 

 

merath01_0-1594159281926.png

 

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



Did I answer your question? Mark my post as a solution!
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
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.

 

edhans_0-1594163576034.png

 

 

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 I answer your question? Mark my post as a solution!
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
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.

edhans_0-1594242328078.png

 



Did I answer your question? Mark my post as a solution!
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
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.

17.png

Cumulative sales =
CALCULATE (
    SUM ( 'Table'[sales] ),
    FILTER ( ALLSELECTED ( 'Table' ), [index] <= MAX ( 'Table'[index] ) )
)

18.png

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
Super User IV

Re: Create a formula for cumulative total

Hi,

You may download my solution file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
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
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 I answer your question? Mark my post as a solution!
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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

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!

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