Question
Evgeny Shvarov · Mar 31, 2016

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!

 

 

 

0
0 344
Discussion (3)0
Log in or sign up to continue

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.