Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
im using a cumulative calculation,
if i put this measure into a table, it works fine. However when i put it into a pivot table with a field in the row dimension, it doesnt take this into account and the results are incorrect.
my formula is:
rangesum(above(total count(distinct [Demand_ID]) - count(distinct [%ID]), 0, rowno(Total)))
as you can see the table is correctly giving me the cumulative total, however when i put the same formula into a pivot table, its giving me the same results as the "difference" column.
the field i use for my row dimenson is "Area". do i need to put this field into my formula above so it takes this into account?
each ID is assigned to one of the values in the "Area" field so i want to see the cumulative variance for each area over the weeks.
thanks for any ideas!
You should be looking at something along the lines of:
RangeSum(Before( Sum(Data),0,ColumnNo()))
Load * INLINE [
Week, Area, Data
1, A, 0
2, A, 2
3, A, 1
1, B, 5
2, B, 0
3, B, 1
];
Not sure if I'm reading this correctly, but if I am, it seems you're looking for the Before() function, rather than Above():
hi @Or thank you for your reply.
if i replace above() with before(), im still getting the same results.
ive attached some sample data for reference. i filtered it to one "area" but we can assume there are multiple areas.
the 3rd column is my measure where i want the cumulative total to start
let me know what you think,
thank you
You should be looking at something along the lines of:
RangeSum(Before( Sum(Data),0,ColumnNo()))
Load * INLINE [
Week, Area, Data
1, A, 0
2, A, 2
3, A, 1
1, B, 5
2, B, 0
3, B, 1
];