Cumulative Sum/ Running Total | MDX

Question

I have an SSAS Cube requirement (MDX) as below:

I am trying to create a calculated measure “Cumulative Sum/ Running Total” with the combination of multiple dimensions (around 7 dimensions). In addition, the users will be using any dimensions that they want. For example, we have a Product, Program, SubProgram, ProgramStatus, SubProgramStatus, Customer, and Date. The users should be able to add the dimension’s attribute to the lowest level as well as they should be able to roll-up the data to a higher level by excluding some of the dimensions such as the Product or SubProgramStatus, or both. Please note the users will be using the Report Layout as a Tabular Form and the can they should have the freedom to slice and dice.

I know how to create the cumulative measure by using the Date dimension such as YTD, but not sure how to create the MDX by including all the scenarios that the users might do, by including and excluding any dimension or attributes that they want. Beside, the users will have another non-cumulative measure that contains the base number for the cumulative measure, and the users will want to add the non-cumulative and cumulative side by side. By adding the cumulative and non-cumulative measures, the roll-up should show how the number increase. Please see the sample.

Any suggestion would be appreciated.

MoazRub MoazRub · Accepted Answer · 2019-01-16T01:13:02

Take a look at the sample query below. The Idea is that if you know the inner most attribute your Measure will be able to perform running total

with 
member 
[Measures].[Internet Sales AmountRunningtotal]
as 
case when [Measures].[Internet Sales Amount] = null then null 
else 
sum({[Product].[Subcategory].firstchild:[Product].[Subcategory].currentmember},[Measures].[Internet Sales Amount])
end
select {[Measures].[Internet Sales Amount],
[Measures].[Internet Sales AmountRunningtotal]
} on columns,

non empty
([Date].[Calendar Year].[Calendar Year],[Date].[Calendar Quarter of Year].[Calendar Quarter of Year],
[Product].[Category].[Category],[Product].[Subcategory].[Subcategory])
on 
rows 
from 
[Adventure Works]

results

Cumulative Sum/ Running Total | MDX

1 Answers