Forum Discussion
Cumulative Totals using measure
Hi Anonymous
In your table, the Desired column has some error from row DayNo 0 to 4, it should be 7688 1424 1119 920 790 692.
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]
Best Reagrds
Maggie
Community ChampionAnonymous 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])
Hope this helps !!
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)
- PattemManohar
Anonymous Ok got you !! Then, please try this..
Test47Out1 = CALCULATE(SUM(Test47CummSum[Mins]),FILTER(ALL(Test47CummSum),MAX(Test47CummSum[DayNo]) <= Test47CummSum[DayNo]))
This doesnt appear to be working when using a measure:
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]))
v-juanli-msftCommunity Support
Hi Anonymous
In your table, the Desired column has some error from row DayNo 0 to 4, it should be 7688 1424 1119 920 790 692.
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]
Best Reagrds
Maggie