Forum Discussion
Cumulative Totals using measure
I have the following simple matrix.
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.
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
5 Replies
- PattemManohar
Community Champion
Anonymous 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 !!
- PattemManohar
Anonymous Ok got you !! Then, please try this..
Test47Out1 = CALCULATE(SUM(Test47CummSum[Mins]),FILTER(ALL(Test47CummSum),MAX(Test47CummSum[DayNo]) <= Test47CummSum[DayNo]))
