Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Cumulative Sum via Calculated Measure Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, October 28, 2013 7:58 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, September 7, 2016 11:17 AM Points: 37, Visits: 200
 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 1from [Adventure Works]`
Post #1508893
 Posted Tuesday, October 29, 2013 4:12 AM
 Ten Centuries Group: General Forum Members Last Login: Today @ 5:39 AM Points: 1,212, Visits: 2,248
 Something like this would work:`WITH MEMBER [C] ASSUM(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 1FROM[Your Cube]`Hope that helps I'm on LinkedIn
Post #1509249
 Posted Tuesday, November 17, 2015 9:51 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, July 14, 2016 1:49 PM Points: 1, Visits: 63

Help me.

With a dimension works.

If you use two attributes of the same dimension:

Year;
Month.

Does not work.

Example 01:

WITH
MEMBER [Measures].[SALDO_INICIAL] AS SUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.PREVMEMBER, [Measures].[QTDE_TOTAL_ENTRADAS] - [Measures].[QTDE_TOTAL_SAIDAS])
MEMBER [Measures].[SALDO_FINAL] AS SUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.CurrentMember, [Measures].[QTDE_TOTAL_ENTRADAS] - [Measures].[QTDE_TOTAL_SAIDAS])

SELECT
{
[Measures].[SALDO_INICIAL],
[Measures].[QTDE_TOTAL_SAIDAS],
[Measures].[SALDO_FINAL]
} ON 0,
[DATAS].[MES].[MES] ON 1
FROM
[Model]

Attachment: img1.jpg

Example 02:

WITH
MEMBER [Measures].[SALDO_INICIAL] AS SUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.PREVMEMBER, [Measures].[QTDE_TOTAL_ENTRADAS] - [Measures].[QTDE_TOTAL_SAIDAS])
MEMBER [Measures].[SALDO_FINAL] AS SUM(NULL:Axis(1).Item(0).Item(Axis(1).Item(0).Count-1).Hierarchy.CurrentMember, [Measures].[QTDE_TOTAL_ENTRADAS] - [Measures].[QTDE_TOTAL_SAIDAS])

SELECT
{
[Measures].[SALDO_INICIAL],
[Measures].[QTDE_TOTAL_SAIDAS],
[Measures].[SALDO_FINAL]
} ON 0,
(
[DATAS].[ANO].[ANO],
[DATAS].[MES].[MES]
) ON 1
FROM
[Model]

Attachment: img2.jpg

Post Attachments
 img1.jpg (5 views, 39.82 KB)
 img2.jpg (2 views, 51.83 KB)
Post #1737466

 Permissions