cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rax99 Member
Member

Cumulative Totals using measure

 

I have the following simple matrix.

 

 

CT.JPG

 

 

 

 

 

 

 

 

I need to be able to cumulativly add up the mins value for that dayno. and the previous cumulative value. So in this example above, the desired values are what I am after.

 

So for Day 0, its 6264 + 1423 = 7687,

           Day 1 its 305 + 1118 = 1423,

           Day 2 its 199 + 919 = 1118,

           , etc

The only thing I will say is that in the full version of this, the [Mins] values are measures rather than stored values.

 

It sounds very simple but im scratching my head over this. Ive attached the pbix file/excel sheet to illustrate the simple formula used for this.

Excel

PBIX

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Cumulative Totals using measure

Hi @rax99

In your table, the Desired column has some error from row DayNo 0 to 4, it should be 7688 1424 1119 920 790 692.

5.png

 

In my test, create a Measure as [Mins] in your original dataset, then calculate based on the [Mins] measure

Measure 2 = SUMX(FILTER(ALL(Sheet1),[DayNo]>=MAX([DayNo])+1),[Measure])

Measure 3 = [Measure]+[Measure 2]

3.png

 

4.png

 

Best Reagrds

Maggie

5 REPLIES 5
Super User
Super User

Re: Cumulative Totals using measure

@rax99 Please try as below.

 

First, add a new calculated column where you can have the PrevDesired value as below

 

PrevDesired = LOOKUPVALUE(Test47CummSum[Desired],Test47CummSum[DayNo],Test47CummSum[DayNo]+1) 

Then, create a measure as below

 

Test47Out = SUM(Test47CummSum[Mins])+SUM(Test47CummSum[PrevDesired])

image.png

 

Hope this helps !!



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





rax99 Member
Member

Re: Cumulative Totals using measure

The only issue is that I dont actually have the 'desired' values in the actual dataset. This was just to illustrate what I needed the results to appear like. I only have the [Mins] values (as measures)

Super User
Super User

Re: Cumulative Totals using measure

@rax99 Ok got you !! Then, please try this..

 

Test47Out1 = CALCULATE(SUM(Test47CummSum[Mins]),FILTER(ALL(Test47CummSum),MAX(Test47CummSum[DayNo]) <= Test47CummSum[DayNo]))


Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





rax99 Member
Member

Re: Cumulative Totals using measure

This doesnt appear to be working when using a measure:

Cumulative.JPG

 

 

 

 

As you can see; 6264 + 1162 does not equal 8540, 

                         305 + 901 does not equal 1162, 

                         etc.

 

However trying this on my example workbook where values are column stored, this does work. Any ideas why the measure is throwing it off?

 

The calc I used is:

RepeatMinsSaved(Cummulative) = CALCULATE([Repeat Mins Saved],FILTER(ALL('Table'),MAX('Table'[DayNo]) <= 'Table'[DayNo]))

 

Community Support Team
Community Support Team

Re: Cumulative Totals using measure

Hi @rax99

In your table, the Desired column has some error from row DayNo 0 to 4, it should be 7688 1424 1119 920 790 692.

5.png

 

In my test, create a Measure as [Mins] in your original dataset, then calculate based on the [Mins] measure

Measure 2 = SUMX(FILTER(ALL(Sheet1),[DayNo]>=MAX([DayNo])+1),[Measure])

Measure 3 = [Measure]+[Measure 2]

3.png

 

4.png

 

Best Reagrds

Maggie