cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Cumulative sum of a measure

Hello guys. I think this should be an easy one, but I have no ideas..

 

I have this table where (by day) I have a list of Sales. Also I have a Objective column.. What I need to do is to see the Difference between these two colums (I mean Sales - Obj) and then a new column which shows a cumulative sum of the Difference.. Example:

 

       Day           Obj    Sales      Diff     Diff cumulative

01/01/2018   1200   1330       130             130

02/01/2018   1399   1100      -299           -169

03/01/2018   344     131        -213           -382

04/01/2018   414     2323       1909          1527

05/01/2018   235     414          87              1614

06/01/2018   2535   242        -2293         -679

07/01/2018   2353   412        -1941         -2620

 

Thanks you all guys!

 

OC

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Cumulative sum of a measure

Hi @omarevp

 

First, create your new column :

 

Diff = YourTable[Sales] - YourTable[Obj]

Then add a new column 

 

Diff cumulative =
CALCULATE (
    SUM (YourTable[Diff] ),
    ALL ( YourTabke),
    YourTable[Day] <= EARLIER (yourTable[Day])
)

Have a good day

 

- Quentin

View solution in original post

4 REPLIES 4
Highlighted
Solution Sage
Solution Sage

Re: Cumulative sum of a measure

Hi @omarevp

 

First, create your new column :

 

Diff = YourTable[Sales] - YourTable[Obj]

Then add a new column 

 

Diff cumulative =
CALCULATE (
    SUM (YourTable[Diff] ),
    ALL ( YourTabke),
    YourTable[Day] <= EARLIER (yourTable[Day])
)

Have a good day

 

- Quentin

View solution in original post

Highlighted
Helper II
Helper II

Re: Cumulative sum of a measure

Hello @quentin_vigne the thing is, SUM function doesnt allow a measure as an argument.

Highlighted
Helper II
Helper II

Re: Cumulative sum of a measure

@quentin_vigneand thanks a lot! Have any idea?

 

greetings 😄

Highlighted
Solution Sage
Solution Sage

Re: Cumulative sum of a measure

@omarevp

 

Your first column should not be a measure but a calculated column. You want the Diff for each row 

Create your difference as a calculated column and it should work 

 

- Quentin

Helpful resources

Announcements
Community Blog

Community Blog

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

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors