Cumulative function over different year
I am trying to calculate cumulative sum over 3 different years, so all previous [Axis.X] does not work as it calculates all three year togheter and I need cum sum per each year. How to do that in calculated column?
Is is possible that cumulative will change based on hierarchy used on graph?
The custom expression would be different for a calculated column and an axis column. But you can start with the Interset() function.
For a calculated column, use:
Sum([Value]) OVER (Intersect([Year],AllPrevious([ID])))
For a bar chart, if the x-axis is e.g.:
<[Year] NEST [ID]>
then for the y-axis, use:
Sum([Value]) OVER (Intersect(Parent([Axis.X]),AllPrevious([Axis.X])))