Log in  ::  Register  ::  Not logged in

## Cumulative Sum via Calculated Measure

 Author Message rossnruthie SSC Journeyman Group: General Forum Members Points: 81 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]` PB_BI SSCarpal Tunnel Group: General Forum Members Points: 4415 Visits: 2736 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 bonamim.jb Grasshopper Group: General Forum Members Points: 17 Visits: 63 Please,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_ENTRADAS], [Measures].[QTDE_TOTAL_SAIDAS], [Measures].[SALDO_FINAL] } ON 0, [DATAS].[MES].[MES] ON 1FROM [Model]Attachment: img1.jpgExample 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_ENTRADAS], [Measures].[QTDE_TOTAL_SAIDAS], [Measures].[SALDO_FINAL] } ON 0, ( [DATAS].[ANO].[ANO], [DATAS].[MES].[MES] ) ON 1FROM [Model]Attachment: img2.jpg Attachments img1.jpg (35 views, 39.00 KB) img2.jpg (29 views, 51.00 KB)