Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
merath01
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
Ashish_Mathur
Super User
Super User

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/

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

What problem do you face?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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



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

@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
edhans
Super User
Super User

  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

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

  

 

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 as solution to help other members find it more quickly.

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

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!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.