Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I need a little help with calculating a running total
Dim1 | Dim2 | Dim3 | Measure1 | Running total |
---|---|---|---|---|
ABC | 1/1/2010 | buy | 100 | 100 |
ABC | 2/1/2010 | buy | 100 | 200 |
ABC | 3/1/2010 | sell | -50 | 150 |
DEF | 1/1/2010 | buy | 150 | 150 |
DEF | 2/1/2010 | sell | -50 | 100 |
HIJ | 1/1/2010 | buy | 300 | 300 |
The running total needs to reset when DIM 1 changes.
thanks for your assistance
Create a Straight Table
Dimension
Dim1
Dim2
Dim3
Expressions
SUM(Measure1)
Aggr(RangeSum(Above(TOTAL SUM(Measure1),0,RowNo())),Dim1,Dim2)
Make sure you sort your straight table by Dim1, Dim2 and Dim3 respectively.
You can also use.
SUM(Aggr(RangeSum(Above(TOTAL SUM(Measure1),0,RowNo())),Dim1,Dim2))