Running total without %CELL
Hi!
There is an example in Samples which shows how to implement 'running total' measure: "Pareto Chart for Category" dashboard.
To calculate running total this pivot uses calculated measure with %Cell expression:
Measures.[Revenue as Percent of Total] + %CELL(0,-1)
And the MDX looks like:
WITH MEMBER [MEASURES].[Revenue as Percent of Total] AS 'Measures.[Amount Sold] / %MDX("select Measures.[Amount sold]on 1 from holefoods","%CONTEXT","filters|columns")',FORMAT_STRING='##.##%;;;;' MEMBER [MEASURES].[Revenue Percent Cumulative] AS 'Measures.[Revenue as Percent of Total] + %CELL(0,-1)',FORMAT_STRING='##.##%;;;;' SELECT {[Measures].[Amount Sold],[MEASURES].[REVENUE PERCENT CUMULATIVE]} ON 0,NON EMPTY ORDER([Product].[P1].[Product Category].Members,Measures.[Amount Sold],BDESC) ON 1 FROM [HoleFoods]
DeepSee specialists! I wonder, is there any way to make pivots with 'running totals' without %CELL expression?
Thank you in advance!
Sure. You could use %CELLZERO instead:
WITH MEMBER [MEASURES].[Revenue as Percent of Total] AS 'Measures.[Amount Sold] / %MDX("select Measures.[Amount sold] on 1 from holefoods","%CONTEXT","filters|columns")',FORMAT_STRING='##.##%;;;;' MEMBER [MEASURES].[Revenue Percent Cumulative] AS 'Measures.[Revenue as Percent of Total] + %CELLZERO(0,-1)', FORMAT_STRING='##.##%;;;;' SELECT {[Measures].[Amount Sold],[MEASURES].[REVENUE PERCENT CUMULATIVE]} ON 0, NON EMPTY ORDER([Product].[P1].[Product Category].Members,Measures.[Amount Sold],BDESC) ON 1 FROM [HoleFoods]Jokes aside, why do you need to remove %CELL usage?
With %CELL you should be always aware when you add any dimension, measure or property in MDX if you need to change %CELL expression too due to new columns rows or because of deletions of rows and cols.
It seems Alex Koblov suggested solution for this in this thread