cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omarevp Regular Visitor
Regular Visitor

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
quentin_vigne Senior Member
Senior Member

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

4 REPLIES 4
Highlighted
quentin_vigne Senior Member
Senior Member

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

omarevp Regular Visitor
Regular Visitor

Re: Cumulative sum of a measure

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

omarevp Regular Visitor
Regular Visitor

Re: Cumulative sum of a measure

@quentin_vigneand thanks a lot! Have any idea?

 

greetings Smiley Very Happy

quentin_vigne Senior Member
Senior Member

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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 110 members 1,785 guests
Please welcome our newest community members: