Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cumulative Sum via Calculated Measure Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 7:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 12:42 PM
Points: 37, Visits: 159
Hey all I'm hoping you can help me with an MDX problem I am having. I will use adventure works for this example. Basically I have been asked to create a calculated measure in the cube that will display a cumulative or rolling sum of another measure. I have found quite few solutions but all of them use a specific dimension such as date with YTD in the SUM but I was hoping to create a measure that was not dependent on any specific dimension.

The simplest MDX I could find is in the example below. While this works in MDX on the fly, I can't use it in Excel in any other dimension besides the product line. I was hoping there was a way to make it dynamic so that it would provide the cumulative sum no matter what dimensions are dropped on.

I was thinking that i would have to somehow use the Descendants function with a specified Axis but my knowledge of MDX is so limited that I am probably way off. Any ideas?

with member [Measures].[Running total] as
Sum(null : [Product].[Product Line].CurrentMember, [Measures].[Internet Sales Amount]),
Format = "Currency"

select {[Measures].[Internet Sales Amount],[Measures].[Running total]} on 0,
[Product].[Product Line].[Product Line].Members on 1
from [Adventure Works]

Post #1508893
Posted Tuesday, October 29, 2013 4:12 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:10 AM
Points: 607, Visits: 1,163
Something like this would work:
WITH MEMBER [C] 
AS
SUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.CurrentMember, [Measures].[Your Measure])

SELECT
{[Measures].[Your Measure], [c]} ON 0,
[Your Dimension].[Your Hierarchy or Attribute] ON 1
FROM
[Your Cube]


Hope that helps





I'm on LinkedIn
Post #1509249
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse